"WITH" SQL statement within HP Quality Center - Invalid Statements - Oracle DB

Question

I am receiving an error when attempting to run my SQL query within HP Quality Center. The message is "Quality Center cannot run the query because it contains invalid statements. The database is Oracle. My query is:

WITH AUDIT AS
(
SELECT AUDIT_LOG.AU_ENTITY_ID AS DEFECT_ID,
       MIN(AUDIT_LOG.AU_TIME) AS Date_Deferred,
       AUDIT_LOG.AU_USER AS UserName_Deferred
FROM AUDIT_LOG
INNER JOIN AUDIT_PROPERTIES
ON AUDIT_LOG.AU_ACTION_ID = AUDIT_PROPERTIES.AP_ACTION_ID
WHERE AUDIT_LOG.AU_ENTITY_TYPE = 'BUG' 
  AND AUDIT_LOG.AU_ACTION = 'UPDATE' 
  AND AUDIT_PROPERTIES.AP_TABLE_NAME = 'BUG' 
  AND AUDIT_PROPERTIES.AP_FIELD_NAME = 'BG_STATUS' 
  AND AUDIT_PROPERTIES.AP_NEW_VALUE = 'Deferred'
GROUP BY
    AUDIT_LOG.AU_ENTITY_ID,AUDIT_LOG.AU_USER
),

WITH BUG_LIST AS
(
SELECT BUG.BG_BUG_ID/*Defect.Defect ID*/, BUG.BG_DETECTION_DATE /*Defect.Detected on Date*/,  BUG.BG_USER_01 /*Defect.Application*/, BUG.BG_SEVERITY /*Defect.Severity*/, BUG.BG_STATUS /*Defect.Status*/,  BUG.BG_USER_11 /*Defect.Planned Closing Date*/,  BUG.BG_DETECTED_BY /*Defect.Detected By*/
FROM   BUG /*Defect*/
WHERE  BUG.BG_USER_17 /*Defect.Finding Group*/ = 'UAT'
ORDER BY  BUG.BG_DETECTION_DATE ASC
)

SELECT *
FROM BUG_LIST
INNER JOIN AUDIT ON AUDIT.DEFECT_ID = BUG_LIST.BG_BUG_ID

The "AUDIT" query is looking for any bug in QC that has, at some point, had its status changed to "Deferred". This contains bugs from all teams using HPQC. The "BUG_LIST" query is looking specifically for bugs that were found by the "UAT" group. I can run each query separately and am attempting to combine the two so that I get a list of bugs found by "UAT". If a bug found by UAT has a deferred date status change associated with it, display this info and if not, leave this info blank for the specific defect.

The reason I am attempting this is because any bugs going into a "Deferred" Status do not have a "planned closing date" like closed bugs do. Because of this, rather than the close date, I would like to see the date the bug moved into deferral status and consider that my "close date".

Does HPQC not support "WITH" statements? Is there some way I can successfully combine and run these queries?

I believe that WITH appeared in 9i. – EvilTeach Dec 05 '12 at 21:54 — EvilTeach, Dec 05 '12 at 21:54

Gordon Linoff · Answer 1 · 2012-12-05T21:43:50.600

You only need the with to appear once, so remove the with before BUG_LIST:

   WITH AUDIT AS
(
SELECT
AUDIT_LOG.AU_ENTITY_ID AS DEFECT_ID,
MIN(AUDIT_LOG.AU_TIME) AS Date_Deferred,
AUDIT_LOG.AU_USER AS UserName_Deferred
FROM AUDIT_LOG
INNER JOIN AUDIT_PROPERTIES
ON AUDIT_LOG.AU_ACTION_ID = AUDIT_PROPERTIES.AP_ACTION_ID
WHERE AUDIT_LOG.AU_ENTITY_TYPE = 'BUG' AND AUDIT_LOG.AU_ACTION = 'UPDATE' AND AUDIT_PROPERTIES.AP_TABLE_NAME = 'BUG' AND AUDIT_PROPERTIES.AP_FIELD_NAME = 'BG_STATUS' AND AUDIT_PROPERTIES.AP_NEW_VALUE = 'Deferred'
GROUP BY
AUDIT_LOG.AU_ENTITY_ID,AUDIT_LOG.AU_USER
),

    BUG_LIST AS
(
SELECT BUG.BG_BUG_ID/*Defect.Defect ID*/, BUG.BG_DETECTION_DATE /*Defect.Detected on Date*/,  BUG.BG_USER_01 /*Defect.Application*/, BUG.BG_SEVERITY /*Defect.Severity*/, BUG.BG_STATUS /*Defect.Status*/,  BUG.BG_USER_11 /*Defect.Planned Closing Date*/,  BUG.BG_DETECTED_BY /*Defect.Detected By*/
FROM   BUG /*Defect*/
WHERE  BUG.BG_USER_17 /*Defect.Finding Group*/ = 'UAT'
ORDER BY  BUG.BG_DETECTION_DATE ASC
)

SELECT *
FROM BUG_LIST
INNER JOIN AUDIT ON AUDIT.DEFECT_ID = BUG_LIST.BG_BUG_ID

You can always combine these into a single from statement using subqueries:

select *
from (your bug_list query here) bl inner join
     (your audit query here) a
     on a.defect_id = bl.bd_bug_id

I was thinkning the same thing but I updated it and still receive the same error. Any other ideas? — pkracer, Dec 05 '12 at 21:40
one would note that the order by in BUG_LIST doesn't really have any effect other than making the query run longer. — EvilTeach, Dec 05 '12 at 21:55
@EvilTeach . . . Whether or not it runs longer depends on the SQL engine. In many cases, the `order by` in subqueries is just ignored. — Gordon Linoff, Dec 05 '12 at 22:06
I will make note of that. The queries separately only take a few seconds to run. — pkracer, Dec 05 '12 at 22:17

score 0 · Answer 2 · answered Dec 05 '12 at 22:01

WITH is generally used to allow you to reuse one of the with clauses. In this case it is not needed, so the query could be rewritten something like this. (i am not in a position to test this)

SELECT audit.*, bug_list.*
FROM 
(
    SELECT BUG.BG_BUG_ID/*Defect.Defect ID*/, 
           BUG.BG_DETECTION_DATE /*Defect.Detected on Date*/,  
           BUG.BG_USER_01 /*Defect.Application*/, 
           BUG.BG_SEVERITY /*Defect.Severity*/, 
           BUG.BG_STATUS /*Defect.Status*/,  
           BUG.BG_USER_11 /*Defect.Planned Closing Date*/,  
           BUG.BG_DETECTED_BY /*Defect.Detected By*/
    FROM   BUG /*Defect*/
    WHERE  BUG.BG_USER_17 /*Defect.Finding Group*/ = 'UAT'
) BUG_LIST,
(
    SELECT AUDIT_LOG.AU_ENTITY_ID AS DEFECT_ID,
           MIN(AUDIT_LOG.AU_TIME) AS Date_Deferred,
           AUDIT_LOG.AU_USER AS UserName_Deferred
    FROM AUDIT_LOG
    INNER JOIN AUDIT_PROPERTIES
    ON AUDIT_LOG.AU_ACTION_ID = AUDIT_PROPERTIES.AP_ACTION_ID
    WHERE AUDIT_LOG.AU_ENTITY_TYPE = 'BUG' 
      AND AUDIT_LOG.AU_ACTION = 'UPDATE' 
      AND AUDIT_PROPERTIES.AP_TABLE_NAME = 'BUG' 
      AND AUDIT_PROPERTIES.AP_FIELD_NAME = 'BG_STATUS' 
      AND AUDIT_PROPERTIES.AP_NEW_VALUE = 'Deferred'
    GROUP BY
        AUDIT_LOG.AU_ENTITY_ID,AUDIT_LOG.AU_USER
) AUDIT
INNER JOIN AUDIT 
ON AUDIT.DEFECT_ID = BUG_LIST.BG_BUG_ID
ORDER BY  BUG_list.BG_DETECTION_DATE ASC

Thanks for the feedback. The query runs with a different error. "ORA-00936: missing expression." — pkracer, Dec 05 '12 at 22:16

"WITH" SQL statement within HP Quality Center - Invalid Statements - Oracle DB

2 Answers2