It's all about the answers!

Ask a question

How to change the output Value in a Select statement based on certain Criteria


Richard Kissel (30617) | asked Mar 06 '18, 8:39 p.m.

CLM v6.0.4 iFix007

Here is my DB2 SQL for my JRS Report Builder Report against a RQM Project.

as part of my report I am listing all the status for each Test Script within my Test Plan. If a script has not been run then it shows a Blank (Unassigned) value for Verdict. Instead of Blank I want it to Say "No Run". So in the SQL Query or the Custom expression I am trying to change the value for the Verdict (T4.VERDICT in the code below) if it is Unassigned to say "No Run" otherwise display what the value for that row.

I am getting an error so I know i am doing it wrong but cannot figure it out, if someone can assist with a solution it would be greatly appreciated...Thank you

SELECT DISTINCT T1.PROJECT_NAME,
       T1.NAME,
       CONCAT(CONCAT(T6.TERM, '/'), T6.VALUE) AS link,
       T2.NAME AS NAME1,
       T3.NAME AS NAME2,
       T4.OWNER_NAME,
       T7.VAR AS VAR,
       T4.START_DATE,
       T5.REFERENCE_ID,
       T5.NAME AS NAME3,
       T5.REQUEST_STATE,
       T3.TYPE,
       CASE WHEN (T4.VERDICT = 'Unassigned')
            THEN T4.VERDICT = 'No Run'
       ELSE T4.VERDICT END as Verdict
FROM RIDW.VW_TESTPLAN T1
INNER JOIN RIDW.VW_TESTPLAN_TESTCASE_LOOKUP LT1
ON (T1.TESTPLAN_ID = LT1.TESTPLAN_ID)
  INNER JOIN RIDW.VW_TESTCASE T2
  ON (T2.TESTCASE_ID = LT1.TESTCASE_ID) AND T2.PROJECT_ID IN (273, 278)
    INNER JOIN RIDW.VW_TESTCASE_TESTSCRIPT_LOOKUP LT2
    ON (T2.TESTCASE_ID = LT2.TESTCASE_ID)
      INNER JOIN RIDW.VW_TESTSCRIPT T3
      ON (T3.TESTSCRIPT_ID = LT2.TESTSCRIPT_ID) AND T3.PROJECT_ID IN (273, 278)
        LEFT OUTER JOIN RIDW.VW_EXECUTION_RESULT T4
        ON (T3.TESTSCRIPT_ID = T4.TESTSCRIPT_ID) AND T4.PROJECT_ID IN (273, 278)
          LEFT OUTER JOIN RIDW.VW_EXECRES_REQUEST_LOOKUP LT3
          ON (T4.EXECUTION_RESULT_ID = LT3.EXECUTION_RESULT_ID)
            LEFT OUTER JOIN RIDW.VW_REQUEST T5
            ON (T5.REQUEST_ID = LT3.REQUEST_ID) AND T5.PROJECT_ID IN (273, 278)
              LEFT OUTER JOIN RIDW.VW_TESTPLAN_CATEGORY_LOOKUP LT4
              ON (T1.TESTPLAN_ID = LT4.TESPLAN_ID)
                LEFT OUTER JOIN RIDW.VW_TEST_CATEGORY T6
                ON LT4.TEST_CATEGORY_ID = T6.TEST_CATEGORY_ID
                  LEFT OUTER JOIN RICALM.VW_TESTSCRIPT_TIMESTAMP_EXT T7
                  ON T7.TESTSCRIPT_ID=T3.TESTSCRIPT_ID AND T7.NAME='Planned Execution Date'
WHERE T1.PROJECT_ID IN (273, 278)  AND
(  T1.NAME = 'HP MBE Q 2018 04 UAT' AND
  T3.TYPE = 'com.ibm.rqm.planning.common.scripttype.manual' AND
  (T5.REQUEST_TYPE = 'Defect'  OR (T5.REQUEST_TYPE='Info not available' OR T5.REQUEST_TYPE='Unassigned' OR T5.REQUEST_TYPE=' ' OR T5.REQUEST_TYPE IS NULL))
) AND
(T1.ISSOFTDELETED = 0 AND T2.ISSOFTDELETED = 0 AND T3.ISSOFTDELETED = 0) AND
(T1.TESTPLAN_ID <> -1 AND T1.TESTPLAN_ID IS NOT NULL) AND
(T2.TESTCASE_ID <> -1 AND T2.TESTCASE_ID IS NOT NULL) AND
(T3.TESTSCRIPT_ID <> -1 AND T3.TESTSCRIPT_ID IS NOT NULL) ORDER BY T1.PROJECT_NAME asc,
         T1.NAME asc,
         T2.NAME asc,
         T3.NAME asc,
         T4.START_DATE desc

Accepted answer


permanent link
Donald Nong (14.5k414) | answered Mar 06 '18, 10:18 p.m.

Try this:
CASE
    WHEN (T4.VERDICT = 'Unassigned') THEN 'No Run'
    ELSE T4.VERDICT
END as Verdict

Richard Kissel selected this answer as the correct answer

Comments
Richard Kissel commented Mar 07 '18, 12:09 p.m.

Thank You Donald, Your solution did not encounter an error during validation so I was very hopeful, but It did not change the value in the report.

I also tried using:

WHEN (T4.VERDICT = '') THEN 'No Run'

WHEN (T4.VERDICT = ' ') THEN 'No Run'

WHEN (T4.VERDICT = NULL) THEN 'No Run'

So not sure how to get it changed...

Perhaps the below may help in how I am trying to get the data

Trace Relationship:
Test Plan (Required) > Test Case (Required) > Test Script (Optional) > Test Case Result (Optional) > Work Item

Because the Test Scripts do not hold a value that it has been executed or the last execution I am using Test Case Result as an Option to the Test Script so that it will give me all the Results plus if a Test case has not been run then it will show blank fields. Of course I do not wan to show blank fields I want it to say "No Run".


Richard Kissel commented Mar 07 '18, 2:16 p.m.

It worked with double Quotes   WHEN (T4.VERDICT = "") THEN 'No Run'

Your answer


Register or to post your answer.


Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.