Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2011
    Posts
    3

    Question Unanswered: Can I "glue" 2 columns to a resultSet?

    Hello there.

    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|


    The TOTAL1 and TOTAL2 subqueries are fine, proof tested.

    So, the thing is: every time TOTAL2 is 0, the result set is empty, 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.

    Code:
    /* ------------------------------------------------
    Autor: Cleber Baldan Junior
    Data: 11/04/2011
    Objetivo:
    Buscar Casos de Teste de Integração do Projeto sem o detalhamento dos passos.
    -------------------------------------------------- */
    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@','%')
        )
        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
    Last edited by cbaldan; 04-13-11 at 09:59. Reason: to make the question more clear

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Tratar esto:
    Code:
    -- Etc --
        NVL(TOTAL1.Total_Testes_TestFolder, 0),
        NVL(TOTAL2.Total_Testes_Com_Rastr, 0)
      From -- Etc --
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Mar 2011
    Posts
    3

    Cool I got it right!

    LKBrwn_DBA, thanks for giving it a try.

    Using the NVL functiona was a nice catch, but I made a few tries and it didn't work. The INNER JOIN I have with the REQ_NAMES subquery cuts all non-matching results out, and it has to be INNER JOIN.

    A friend which is a DBA helped me and found 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):

    Code:
        |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 subquey get wrapped with the columns I needed.

    Anyway, here is the code:
    ( in the post below, I'm past the forum limit per post )

  4. #4
    Join Date
    Mar 2011
    Posts
    3

    Talking the code!!!

    Code:
    SELECT
        '@SIGA@'               AS SIGA,
        CYCL_FOLD.CF_ITEM_NAME AS Projeto,
        Origem,
        Processo,
        ...
        TOTAL1.Total_Testes_TestFolder,
        TOTAL2.Total_Testes_Com_Rastr
    FROM
        CYCL_FOLD
    LEFT JOIN
        (
            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,
                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      distinct
                        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.Projeto
    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
        ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •