Can I "glue" 2 columns to a resultSet?

I´m working on some queries on Oracle 10g - 10.2.0.40, within a HP Quality Center 9.2 database.

I built a query that will go to a test folder, given by the SIGA key, select only the tests within the given test folder that have correct traceability to the release.

It also has 2 counter subqueries: TOTAL1 sums the total of tests in the test folder. TOTAL2 returns the total amount of tests in the test folder with correct traceability.

The query works fine, but has one flaw: If none of the tests inside the folder are associated with the correct release, the result set returns empty, but even though, I would like the TOTAL1 and TOTAL2 columns to be returned, like this:

|SIGA| |PROJETO| |ORIGEM|........ |TOTAL1| |TOTAL2|
|NULL| |NULL|    |NULL|  ........ |10|     |0|

So, the thing is: every time TOTAL2 is 0, the "main" resultSet is empty, as it should, but even tough I want to have the TOTAL1 and TOTAL2 columns.

Is it possible to "glue" a new column with TOTAL1 and TOTAL2 to the main query even with an empty resultSet? I tried to make LEFT JOIN but didn`t work.

Thanks, Cleber.

SELECT
    '@SIGA@'                 AS SIGA,
    CYCL_FOLD.CF_ITEM_NAME   AS Projeto,
    CYCLE.CY_CYCLE           AS Origem,
    REQ_NAMES.PROCESSO       AS Processo,
    REQ_NAMES.FUNCIONALIDADE AS Funcionalidade,
    ALL_LISTS.AL_DESCRIPTION AS Cenario,
    TEST.TS_TEST_ID          AS Test_ID,
    TESTCYCL.TC_USER_01      AS ID_Cliente,
    TEST.TS_NAME             AS Nome_do_Caso,
    TESTCYCL.TC_USER_02      AS Dependencia,
    TEST.TS_USER_06          AS Tipo_Teste,
    TEST.TS_USER_02          AS Tipo_Cenario,
    /* Descricao */
    REGEXP_REPLACE(CAST(TEST.TS_DESCRIPTION AS VARCHAR2(4000)), '<[^<]+>', NULL) AS Descricao,
    /* Pre_Condicao */
    REGEXP_REPLACE(CAST(TEST.TS_USER_25 AS VARCHAR2(4000)), '<[^<]+>', NULL) AS Pre_Condicao,
    /* Resultado_Esperado */
    REGEXP_REPLACE(CAST(TEST.TS_USER_26 AS VARCHAR2(4000)), '<[^<]+>', NULL) AS Resultado_Esperado,
    /* Dados_Necessarios */
    REGEXP_REPLACE(CAST(TEST.TS_USER_27 AS VARCHAR2(4000)), '<[^<]+>', NULL) AS Dados_Necessarios,
    TESTCYCL.TC_USER_04                                                      AS Resp_Execucao,
    TESTCYCL.TC_STATUS                                                       AS Status,
    TEST.TS_USER_03                                                          AS Sistemas_Impactados,
    TEST.TS_USER_05     AS Regressão,
    TEST.TS_RESPONSIBLE AS Autor_Do_Caso,
    /* Comentario */
    REGEXP_REPLACE(CAST(TEST.TS_DEV_COMMENTS AS VARCHAR2(4000)) , '<[^<]+>', NULL)AS Comentario,
    TOTAL1.Total_Testes_TestFolder,
    TOTAL2.Total_Testes_Com_Rastr
FROM
    (
        SELECT
            COUNT(TESTCYCL.TC_TEST_ID) AS Total_Testes_TestFolder
        FROM
            CYCLE,
            CYCL_FOLD,
            TESTCYCL
        WHERE
            TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
        AND CYCLE.CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID
        AND CYCL_FOLD.CF_ITEM_NAME LIKE CONCAT('@SIGA@开发者_C百科','%')
    )
    TOTAL1,
    (
        SELECT
            COUNT(DISTINCT CONCAT(TESTCYCL.TC_TESTCYCL_ID, TESTCYCL.TC_TEST_ID)) AS
            Total_Testes_Com_Rastr
        FROM
            CYCL_FOLD,
            CYCLE,
            TESTCYCL,
            REQ_COVER,
            REQ_CYCLES,
            RELEASE_CYCLES
        WHERE
            TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
        AND cycle.CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID
        AND CYCL_FOLD.CF_ITEM_NAME LIKE CONCAT('@SIGA@','%')
        AND REQ_COVER.RC_ENTITY_ID = TESTCYCL.TC_TEST_ID
        AND REQ_COVER.RC_REQ_ID = REQ_CYCLES.RQC_REQ_ID
        AND REQ_CYCLES.RQC_CYCLE_ID = RELEASE_CYCLES.RCYC_ID
        AND RELEASE_CYCLES.RCYC_NAME LIKE CONCAT('@SIGA@','%')
        ORDER BY
            TESTCYCL.TC_TEST_ID
    )
    TOTAL2,
    CYCL_FOLD
LEFT JOIN RELEASE_CYCLES
ON
    RELEASE_CYCLES.RCYC_ID = CYCL_FOLD.CF_ASSIGN_RCYC
LEFT JOIN CYCLE
ON
    CYCLE.CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID
LEFT JOIN TESTCYCL
ON
    TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
LEFT JOIN TEST
ON
    TEST.TS_TEST_ID = TESTCYCL.TC_TEST_ID
LEFT JOIN ALL_LISTS
ON
    ALL_LISTS.AL_ITEM_ID = TEST.TS_SUBJECT
INNER JOIN
    (
        SELECT
            TESTCYCL.TC_TEST_ID         AS TEST_ID,
            wm_concat(REQ2.RQ_REQ_NAME) AS Processo,
            wm_concat(REQ1.RQ_REQ_NAME) AS Funcionalidade
        FROM
            CYCL_FOLD
        LEFT JOIN CYCLE
        ON
            CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID
        RIGHT JOIN TESTCYCL
        ON
            TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
        RIGHT JOIN REQ_COVER
        ON
            RC_ENTITY_ID = TESTCYCL.TC_TEST_ID
        INNER JOIN REQ REQ1
        ON
            RC_REQ_ID = REQ1.RQ_REQ_ID
        INNER JOIN REQ REQ2
        ON
            REQ1.RQ_FATHER_ID = REQ2.RQ_REQ_ID
        INNER JOIN REQ_TYPE
        ON
            REQ2.RQ_TYPE_ID = REQ_TYPE.TPR_TYPE_ID
            AND REQ_TYPE.TPR_NAME = 'Processo'
        RIGHT JOIN REQ_CYCLES
        ON
            RQC_REQ_ID = REQ1.RQ_REQ_ID
        INNER JOIN RELEASE_CYCLES
        ON
            RQC_CYCLE_ID = RELEASE_CYCLES.RCYC_ID
        AND CF_ASSIGN_RCYC = RELEASE_CYCLES.RCYC_ID
        WHERE
            CF_ITEM_NAME LIKE CONCAT('@SIGA@','%')
        GROUP BY
            TESTCYCL.TC_TEST_ID

        UNION

        SELECT
            TESTCYCL.TC_TEST_ID         AS TEST_ID,
            '' AS Processo,
            wm_concat(REQ1.RQ_REQ_NAME) AS Funcionalidade
        FROM
            CYCL_FOLD
        LEFT JOIN CYCLE
        ON
            CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID
        RIGHT JOIN TESTCYCL
        ON
            TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
        RIGHT JOIN REQ_COVER
        ON
            RC_ENTITY_ID = TESTCYCL.TC_TEST_ID
        INNER JOIN REQ REQ1
        ON
            RC_REQ_ID = REQ1.RQ_REQ_ID
        INNER JOIN REQ REQ2
        ON
            REQ1.RQ_FATHER_ID = REQ2.RQ_REQ_ID
        INNER JOIN REQ_TYPE
        ON
            REQ2.RQ_TYPE_ID = REQ_TYPE.TPR_TYPE_ID
            AND REQ_TYPE.TPR_NAME <> 'Processo'
        RIGHT JOIN REQ_CYCLES
        ON
            RQC_REQ_ID = REQ1.RQ_REQ_ID
        INNER JOIN RELEASE_CYCLES
        ON
            RQC_CYCLE_ID = RELEASE_CYCLES.RCYC_ID
        AND CF_ASSIGN_RCYC = RELEASE_CYCLES.RCYC_ID
        WHERE
            CF_ITEM_NAME LIKE CONCAT('@SIGA@','%')
        GROUP BY
            TESTCYCL.TC_TEST_ID
    )
    REQ_NAMES
ON
    REQ_NAMES.TEST_ID = TEST.TS_TEST_ID
WHERE
    CYCL_FOLD.CF_ITEM_NAME LIKE CONCAT('@SIGA@','%')
ORDER BY
    CYCLE.CY_CYCLE,
    ALL_LISTS.AL_DESCRIPTION,
    REQ_NAMES.PROCESSO,
    TEST.TS_TEST_ID

So if LEFT OUTER JOIN didn't do what you want it might be you're looking for a RIGHT OUTER JOIN or FULL OUTER JOIN.

Like I said above, a friend helped me out to find a solution.

Since the PROJETO record should always exist, I would always have the one row I need to return TOTAL1 and TOTAL2, even when there are 0 tests with correct traceability ( which means TOTAL2 = 0 )

So, what I needed was one query and one subquery.

The first query would look for the PROJETO folder, then LEFT JOIN to a subquery that has all the main logic, then CROSS with TOTAL1 and TOTAL2.

So, in case the project folder exists, has tests, but 0 of them have correct traceability, the outer query would return what I asked (and even better):

|SIGA| |PROJETO         | |ORIGEM|........ |TOTAL1| |TOTAL2|
|1234| |1234 - Project X| |NULL  |........ |10|     |0|

I'm not sure if I can call like this, but I imagine this as a "wrapping query": all records returned in the subquery get wrapped with the columns I needed.

Anyway, here is the code:

SELECT
    '@SIGA@'               AS SIGA,
    CYCL_FOLD.CF_ITEM_NAME AS Projeto,
    Origem,
    Processo,
    Funcionalidade,
    Cenario,
    Test_ID,
    Instancia_Do_Teste,
    ID_Cliente,
    Nome_do_Caso,
    Dependencia,
    Tipo_Teste,
    Tipo_Cenario,
    Descricao,
    Pre_Condicao,
    Resultado_Esperado,
    Dados_Necessarios,
    Resp_Execucao,
    Status_Execução_Caso,
    Sistemas_Impactados_Teste,
    Regressao,
    Autor_Do_Caso,
    Ordem_Dos_Passos,
    Nome_do_Passo,
    Descricao_do_Passo,
    Resultado_Esperado_Passo,
    Sistemas_Impactado_Passo,
    Provedor,
    ST_RUN_ID,
    Status_Execução_Passo,
    TOTAL1.Total_Testes_TestFolder,
    TOTAL2.Total_Testes_Com_Rastr
FROM
    CYCL_FOLD
LEFT JOIN
    (
        SELECT
            CYCL_FOLD.CF_ITEM_NAME,
            '@SIGA@'                 AS SIGA,
            CYCL_FOLD.CF_ITEM_NAME   AS Projeto,
            CYCLE.CY_CYCLE           AS Origem,
            REQ_NAMES.PROCESSO       AS Processo,
            REQ_NAMES.FUNCIONALIDADE AS Funcionalidade,
            ALL_LISTS.AL_DESCRIPTION AS Cenario,
            TEST.TS_TEST_ID          AS Test_ID,
            TC_TEST_INSTANCE         AS Instancia_Do_Teste,
            TESTCYCL.TC_USER_01 AS ID_Cliente,
            TEST.TS_NAME        AS Nome_do_Caso,
            TESTCYCL.TC_USER_02 AS Dependencia,
            TEST.TS_USER_06     AS Tipo_Teste,
            TEST.TS_USER_02     AS Tipo_Cenario,
            /* Descricao */
            REGEXP_REPLACE(CAST(TEST.TS_DESCRIPTION AS VARCHAR2(4000)), '<[^<]+>', NULL) AS
            Descricao,
            /* Pre_Condicao */
            REGEXP_REPLACE(CAST(TEST.TS_USER_25 AS VARCHAR2(4000)), '<[^<]+>', NULL) AS
            Pre_Condicao,
            /* Resultado_Esperado */
            REGEXP_REPLACE(CAST(TEST.TS_USER_26 AS VARCHAR2(4000)), '<[^<]+>', NULL) AS
            Resultado_Esperado,
            /* Dados_Necessarios */
            REGEXP_REPLACE(CAST(TEST.TS_USER_27 AS VARCHAR2(4000)), '<[^<]+>', NULL) AS
            Dados_Necessarios,
            TESTCYCL.TC_USER_04 AS Resp_Execucao,
            TESTCYCL.TC_STATUS  AS Status_Execução_Caso,
            TEST.TS_USER_03     AS Sistemas_Impactados_Teste,
            TEST.TS_USER_05     AS Regressao,
            TEST.TS_RESPONSIBLE AS Autor_Do_Caso,
            /* Comentario */
            REGEXP_REPLACE(CAST(TEST.TS_DEV_COMMENTS AS VARCHAR2(4000)) , '<[^<]+>', NULL)AS
            Comentario,
            DESSTEPS.DS_STEP_ORDER AS Ordem_Dos_Passos,
            DESSTEPS.DS_STEP_NAME  AS Nome_do_Passo,
            /* Descrição do Passo */
            REGEXP_REPLACE(CAST(DESSTEPS.DS_DESCRIPTION AS VARCHAR2(4000)), '<[^<]+>', NULL) AS
            Descricao_do_Passo,
            /* Resultado Esperado do Passo */
            REGEXP_REPLACE(CAST(DESSTEPS.DS_EXPECTED AS VARCHAR2(4000)), '<[^<]+>', NULL) AS
            Resultado_Esperado_Passo,
            DESSTEPS.DS_USER_01 AS Sistemas_Impactado_Passo,
            DESSTEPS.DS_USER_02 AS Provedor,
            STEP.ST_RUN_ID,
            STEP.ST_STATUS AS Status_Execução_Passo
        FROM
            CYCL_FOLD
        LEFT JOIN RELEASE_CYCLES
        ON
            RELEASE_CYCLES.RCYC_ID = CYCL_FOLD.CF_ASSIGN_RCYC
        LEFT JOIN CYCLE
        ON
            CYCLE.CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID
        LEFT JOIN TESTCYCL
        ON
            TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
        LEFT JOIN TEST
        ON
            TEST.TS_TEST_ID = TESTCYCL.TC_TEST_ID
        LEFT JOIN DESSTEPS
        ON
            DESSTEPS.DS_TEST_ID = TEST.TS_TEST_ID
        LEFT JOIN RUN
        ON
            RN_TEST_ID = TEST.TS_TEST_ID
        AND RN_TESTCYCL_ID = TESTCYCL.TC_TESTCYCL_ID
        AND RN_CYCLE_ID = CYCLE.CY_CYCLE_ID
        LEFT JOIN STEP
        ON
            ST_TEST_ID = DS_TEST_ID
        AND ST_DESSTEP_ID = DESSTEPS.DS_ID
        AND ST_RUN_ID = RN_RUN_ID
        LEFT JOIN ALL_LISTS
        ON
            ALL_LISTS.AL_ITEM_ID = TEST.TS_SUBJECT
        INNER JOIN
            (
                SELECT
                    TESTCYCL.TC_TEST_ID         AS TEST_ID,
                    wm_concat(REQ2.RQ_REQ_NAME) AS Processo,
                    wm_concat(REQ1.RQ_REQ_NAME) AS Funcionalidade
                FROM
                    CYCL_FOLD
                LEFT JOIN CYCLE
                ON
                    CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID
                RIGHT JOIN TESTCYCL
                ON
                    TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
                RIGHT JOIN REQ_COVER
                ON
                    RC_ENTITY_ID = TESTCYCL.TC_TEST_ID
                INNER JOIN REQ REQ1
                ON
                    RC_REQ_ID = REQ1.RQ_REQ_ID
                INNER JOIN REQ REQ2
                ON
                    REQ1.RQ_FATHER_ID = REQ2.RQ_REQ_ID
                INNER JOIN REQ_TYPE
                ON
                    REQ2.RQ_TYPE_ID = REQ_TYPE.TPR_TYPE_ID
                AND REQ_TYPE.TPR_NAME = 'Processo'
                RIGHT JOIN REQ_CYCLES
                ON
                    RQC_REQ_ID = REQ1.RQ_REQ_ID
                INNER JOIN RELEASE_CYCLES
                ON
                    RQC_CYCLE_ID = RELEASE_CYCLES.RCYC_ID
                AND CF_ASSIGN_RCYC = RELEASE_CYCLES.RCYC_ID
                WHERE
                    CF_ITEM_NAME LIKE CONCAT('@SIGA@','%')
                GROUP BY
                    TESTCYCL.TC_TEST_ID
                UNION
                SELECT
                    TESTCYCL.TC_TEST_ID         AS TEST_ID,
                    ''                          AS Processo,
                    wm_concat(REQ1.RQ_REQ_NAME) AS Funcionalidade
                FROM
                    CYCL_FOLD
                LEFT JOIN CYCLE
                ON
                    CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID
                RIGHT JOIN TESTCYCL
                ON
                    TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
                RIGHT JOIN REQ_COVER
                ON
                    RC_ENTITY_ID = TESTCYCL.TC_TEST_ID
                INNER JOIN REQ REQ1
                ON
                    RC_REQ_ID = REQ1.RQ_REQ_ID
                INNER JOIN REQ REQ2
                ON
                    REQ1.RQ_FATHER_ID = REQ2.RQ_REQ_ID
                INNER JOIN REQ_TYPE
                ON
                    REQ2.RQ_TYPE_ID = REQ_TYPE.TPR_TYPE_ID
                AND REQ_TYPE.TPR_NAME <> 'Processo'
                RIGHT JOIN REQ_CYCLES
                ON
                    RQC_REQ_ID = REQ1.RQ_REQ_ID
                INNER JOIN RELEASE_CYCLES
                ON
                    RQC_CYCLE_ID = RELEASE_CYCLES.RCYC_ID
                AND CF_ASSIGN_RCYC = RELEASE_CYCLES.RCYC_ID
                WHERE
                    CF_ITEM_NAME LIKE CONCAT('@SIGA@','%')
                GROUP BY
                    TESTCYCL.TC_TEST_ID
            )
            REQ_NAMES
        ON
            REQ_NAMES.TEST_ID = TEST.TS_TEST_ID
        WHERE
            CYCL_FOLD.CF_ITEM_NAME LIKE CONCAT('@SIGA@','%')
        AND
            (
                RN_RUN_ID IS NULL
             OR RN_RUN_ID =
                (
                    SELECT
                        MAX(RN_RUN_ID)
                    FROM
                        RUN
                    WHERE
                        RN_TEST_ID = TEST.TS_TEST_ID
                    AND RN_TEST_INSTANCE = TC_TEST_INSTANCE
                )
            )
    )
    mainQuery
ON
    CYCL_FOLD.CF_ITEM_NAME = mainQuery.CF_ITEM_NAME
CROSS JOIN
    (
        SELECT
            COUNT(TESTCYCL.TC_TEST_ID) AS Total_Testes_TestFolder
        FROM
            CYCLE,
            CYCL_FOLD,
            TESTCYCL
        WHERE
            TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
        AND CYCLE.CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID
        AND CYCL_FOLD.CF_ITEM_NAME LIKE CONCAT('@SIGA@','%')
    )
    TOTAL1
CROSS JOIN
    (
        SELECT
            COUNT(DISTINCT CONCAT(TESTCYCL.TC_TESTCYCL_ID, TESTCYCL.TC_TEST_ID)) AS
            Total_Testes_Com_Rastr
        FROM
            CYCL_FOLD,
            CYCLE,
            TESTCYCL,
            REQ_COVER,
            REQ_CYCLES,
            RELEASE_CYCLES
        WHERE
            TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
        AND cycle.CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID
        AND CYCL_FOLD.CF_ITEM_NAME LIKE CONCAT('@SIGA@','%')
        AND REQ_COVER.RC_ENTITY_ID = TESTCYCL.TC_TEST_ID
        AND REQ_COVER.RC_REQ_ID = REQ_CYCLES.RQC_REQ_ID
        AND REQ_CYCLES.RQC_CYCLE_ID = RELEASE_CYCLES.RCYC_ID
        AND RELEASE_CYCLES.RCYC_NAME LIKE CONCAT('@SIGA@','%')
        ORDER BY
            TESTCYCL.TC_TEST_ID
    )
    TOTAL2
WHERE
    CYCL_FOLD.CF_ITEM_NAME LIKE CONCAT('@SIGA@','%')
ORDER BY
    Origem,
    Funcionalidade,
    Cenario,
    Test_ID,
    Instancia_Do_Teste,
    Ordem_Dos_Passos