Help with Oracle IF statment

kalees
kalees used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
HI,

pls try
 select case when INSTR(FINE_NOTES, '#') >0 then SUBSTR (FINE_NOTES,(INSTR (FINE_NOTES, '#')+1))  else null  end as "Received Date"

Open in new window

Regards
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
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.
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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial