We help IT Professionals succeed at work.

Help with Oracle IF statment

kalees
kalees asked
on
141 Views
Last Modified: 2017-03-14
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.
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
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!

Author

Commented:
Perfect solution, many thanks
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
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.
Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
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!