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, 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!