How to change the output Value in a Select statement based on certain Criteria
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
Try this:
CASE
WHEN (T4.VERDICT = 'Unassigned') THEN 'No Run'
ELSE T4.VERDICT
END as Verdict
Comments
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".
It worked with double Quotes WHEN (T4.VERDICT = "") THEN 'No Run'