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 (30110) | 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.3k213) | 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.