Use Oracle CASE:
SELECT c.CHCTRLID AS "CC ID", c.STATUS3, b.VPTIMSTAMP,
a.VP_DATE AS "Submit Date", a.CRITI_EXP_DT AS "Critical Date", c.REQ_TYPE, c.SUBJLETTER,
c.PGCOUNT AS "Page Total", c.EFFECT_PGCOUNT,
c.PUBTYPE AS "Publication Type", c.COMPLEXITYLEVEL,
c.PUBDATE, a.BRANCH, a.BRANCH_SEC, a.PRIORITY, c.WRITER, a.REQ_INFO_DESCRIPT, a.DEPT,
Abs(Round((PUBDATE-VPTIMSTAMP),0)) AS "SLA Level in Days",
WHEN PUBDATE IS NULL then 'n/a',
END) AS "Secondary SLA",
a.OPS_MAN_CHG, a.OPS_MAN_PPCHAP,a.STRATEGY_UPDATE, a.CRITICAL_EXP
FROM CHG_CTRL_ADM b INNER JOIN CHG_CTRL_USR a
ON b.CHCTRLID = a.CHCTRLID
INNER JOIN ADM_SUPP_INDX c ON b.CHCTRLID = c.CHCTRLID
WHERE c.STATUS3 != 'Cancelled' AND a.VP_DATE > '04-APR-11'
ORDER BY a.CHCTRLID
Decode is possible, but CASE is better readable.
Try this as a column value
CASE WHEN PUBDATE IS NULL then 'n/a' else to_char(Abs(Round(SYSDATE-VPTIMSTAMP),0)) ) end AS "Secondary SLA",
second possibility for NULL check is use NVL or NVL2.
The datatype of both results needs to match.
nvl2(PUBDATE, to_char(Abs(Round(SYSDATE-VPTIMSTAMP))) , 'n/a')
Well, I thought that would do it, but for some reason I'm still getting an 00905 error, missing keyword FROM.
Not really sure why, though. The syntax looks correct. I'll keep tweaking it and see.
missing keyword FROM.
usually means you forgot a comma in the column list.
It also looks like that I added one closing parenthesis too many in the case version. Remove one and test again. Sorry for the typo in that case.
Well, Sven, I wished that had done it but while there may have been one too many closing parentheses characters, removing it and retesting still shows the same error, ORA-00905: missing keyword.
Ok, I'll try the NVL2 function.
Ok, Sven - good news! THIS worked, the NVL2 function. I'll duly document it as such for further embedded SQL queries!
Thank you so much!