Applies To:
SQL Database
Issue:
The customer wants a value written of No Link if a value is not returned in a SQL query.
Solution:
Use a combination of COALESCE and NullIf in the select statement. Example is below:
SELECT
CYCLE.CY_CYCLE as "Test Set Name",
COALESCE(NULLIF(CYCL_FOLD.CF_ITEM_NAME, ''), 'No Link') AS "Test Set Folder Name",
BUG.BG_BUG_ID as "Defect ID",
COALESCE(NULLIF(CONVERT(Varchar(10),LINK.LN_LINK_ID), ''), 'None') as "Link ID",
LINK.LN_ENTITY_TYPE as "Linked Entity Type",
TEST.TS_NAME as "Linked Entity Name",
TEST.TS_TEST_ID AS "Test ID",
LINK.LN_ENTITY_ID as "Linked Entity ID",
BUG.BG_STATUS AS "Status",
BUG.BG_SEVERITY as "Severity",
BUG.BG_DETECTION_DATE as "Detected on Date" ,
BUG.BG_DETECTED_BY as "Detected By",
BUG.BG_USER_01 as "Detected by Team",
BUG.BG_CLOSING_DATE as "Closing Date",
BUG.BG_RESPONSIBLE as "Assigned To",
BUG.BG_PRIORITY as "Priority",
BUG.BG_USER_02 as "Assigned to Team",
STEP.ST_RUN_ID as "Run ID",
BUG.BG_DETECTED_IN_RCYC as "Detected in Cycle",
Case when BUG.BG_DETECTED_IN_RCYC='1006' then 'ITC3'
ELSE 'No' END AS "DETECTED IN CYCLE"
FROM BUG
Left Join LINK On BUG.BG_BUG_ID = LINK.LN_BUG_ID
Left Join STEP on LINK.LN_ENTITY_ID = STEP.ST_ID
Left Join RUN on STEP.ST_RUN_ID = RUN.RN_RUN_ID
Left Join TESTCYCL On RUN.RN_TESTCYCL_ID = TESTCYCL.TC_TESTCYCL_ID
Left Join TEST on TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
Left Join CYCLE On TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
LEFT JOIN CYCL_FOLD ON CYCLE.CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID
where BUG.BG_DETECTED_IN_RCYC in ('1006')
order By BUG.BG_BUG_ID