October 28, 2022 at 1:53 pm
CASE WHEN PM_PROJECT.PROJ_MGR_ID IS NULL THEN
CASE WHEN PM_PROJECT.SCHED_MGR_ID IS NULL THEN ' '
ELSE SCH_MNGR
END
ELSE PROJ_MNGR
END PM_SM,
Option 2
NVL2(PM_PROJECT.SCHED_MGR_ID,SCH_MNGR,PROJ_MNGR) PM_SM,
October 28, 2022 at 2:23 pm
That's Oracle, not TSQL.
What's the error?
NVL2 (or COALESCE) seems like the better option anyway -- simpler, clearer.
October 28, 2022 at 2:52 pm
If this is Oracle I think the case statement syntax is less flexible than t-sql. I think it needs to be in this form. (That's the only way I have managed to make it work)
CASE PM_PROJECT.PROJ_MGR_ID
WHEN NULL THEN
CASE PM_PROJECT.SCHED_MGR_ID
WHEN NULL THEN ''
ELSE SCH_MNGR
END
ELSE PROJ_MNGR
END SM_PM
The NVL2 option doesn't check the null status of the ID columns. Were you trying to keep the null checks to a single table? If not then NVL2(PROJ_MNGR, SCH_MNGR,'') might work.
October 29, 2022 at 1:57 am
CASE is an expression,not a statement. This basic programming not just SQL. you should use COALESCE(<list>) to pick the first non-null value. But more than that , you gave us no DDL. Why did you allow NULL identifiers?
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply