Link to home
Start Free TrialLog in
Avatar of kalees
kaleesFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Help with Oracle IF statment

Hi All,

I am looking to extract a date from a notes box which will always appear after the # symbol. I have achieved this with the following line of code

SUBSTR (FINE_NOTES,(INSTR (FINE_NOTES, '#')+1)) AS "Received Date",

I am now attempting to take it one stage further to return NULL if the # symbol is not present as there is other text in the notes box which I do not require. I have tried with the following code but am getting errors and I am struggling to see the issue.

IF INSTR (FINE_NOTES, '#') THEN SUBSTR(INSTR (FINE_NOTES, '#')+1) ELSE NULL END IF) AS "Received Date",


Any help greatly appreciated,

Keith.
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Or hopefully...

IIF(INSTR (FINE_NOTES, '#') != 0, SUBSTR(INSTR (FINE_NOTES, '#')+1),  NULL) AS "Received Date", 

Open in new window


I am more of a T-SQL developer than PL/SQL one, so please take it with table-spoon of salt!
Avatar of kalees

ASKER

Perfect solution, many thanks
FYI only:

To my knowledge up till now IIF() is not supported by PL/SQL and (I think) also isn't part f the SQL standards either.
Which is why I always prefer case expressions even for T-SQL.
I googled and found it here. So pasted the solution. Only after seeing your posted I noticed that it is part of MDX and not of PL/SQL.

Thank you!