OraFAQ Forum: SQL & PL/SQL » Oracle Function to emulatte an " IIF " statement

Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Function to emulatte an " IIF " statement

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle Function to emulatte an " IIF " statement [message #5103]

Thu, 30 January 2003 03:28

Sandra Taylor
Messages: 10
Registered: October 2002

Junior Member

I'm migrating a MSAccess database to Oracle.
The new application is being written in Visual Basic by a co-worker.
He has incorporated code from the original Access application and it includes an " IIF " statement. He thinks there is a function we can write in oracle that will emulate the IIF portion of this statement.
I've looked around and haven't found anything. Can you help?
Thanks,
Sandra

SELECT DISTINCTROW APPT.ATIME, APPT.SHOWTIME, IIf(IsNull([[WORKTIME]])=False,[[WORKTIME]],Time())-TimeSerial(Hour([[SHOWTIME]]),Minute([[SHOWTIME]]),Second([[SHOWTIME]])) AS Expr3, [[SPONSOR]].[[LASTNAME]]+", "+[[SPONSOR]].[[FIRSTMID]] AS Expr1, Sponsor.Rank, APPT_TYPES.APPT, IIf(IsNull([[workdte]])=True And IsNull([[SHOWDTE]])=False,"WAITING",IIf(IsNull([[SHOWDTE]])=True,"NOT HERE",IIf(IsNull([[SHOWDTE]])=False And IsNull([[WORKDTE]])=False And IsNull([[COMPLETED]])=True,"WORKING",IIf(IsNull([[COMPLETED]])=False,"COMPLETED")))) AS Expr2, APPT.WITH, Sponsor.ID
FROM ((APPT LEFT JOIN Vehicle ON APPT.PLATE = Vehicle.Plate) LEFT JOIN Sponsor ON APPT.ID = Sponsor.ID) LEFT JOIN APPT_TYPES ON APPT.ATYPE = APPT_TYPES.ACODE
WHERE (((IIf(IsNull([[workdte]])=True And IsNull([[SHOWDTE]])=False,"WAITING",IIf(IsNull([[SHOWDTE]])=True,"NOT HERE",IIf(IsNull([[SHOWDTE]])=False And IsNull([[WORKDTE]])=False And IsNull([[COMPLETED]])=True,"WORKING",IIf(IsNull([[COMPLETED]])=False,"COMPLETED")))))<>"COMPLETED") AND ((APPT.ADATE)=Date()) AND ((APPT.CANX) Is Null))
ORDER BY APPT.ATIME, APPT.SHOW

Report message to a moderator

Re: Oracle Function to emulatte an " IIF " statement [message #5107 is a reply to message #5103]

Thu, 30 January 2003 06:24

me
Messages: 66
Registered: August 2000

Member

decode

Report message to a moderator

Re: Oracle Function to emulatte an " IIF " statement [message #5118 is a reply to message #5103]

Thu, 30 January 2003 11:40

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

I can't translate the Access code to Oracle, but maybe this might help get you started:

An expression like:

IIf(IsNull([WORKTIME])=False,[WORKTIME],
Time())-TimeSerial(Hour([SHOWTIME]),Minute([SHOWTIME]),Second([SHOWTIME])) AS Expr3

might look something like:

nvl(worktime, sysdate - showtime) expr3

which means: If the value of WORKTIME is NULL, use the elapsed time since showtime, otherwise use WORKTIME. This assumes that worktime and showtime are numeric values. Again, I am not translating your Access code because I don't know how the TIME and TIMESERIAL functions work.

You can also use CASE expressions which will handle your more complex expressions:

IIf(IsNull([SHOWDTE])=False And IsNull([WORKDTE])=False And IsNull([COMPLETED])=True,"WORKING",
  IIf(IsNull([COMPLETED])=False,"COMPLETED")))) AS Expr2

becomes:

case
when     showdte is not null
     and workdte is not null
     and completed is null 
     then 'WORKING'
when completed is not null 
     then 'COMPLETED'
else 'SOME OTHER CONDITION'
end expr2

Report message to a moderator

Previous Topic:	Number
Next Topic:	How do I delete the first column in a field of table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 15 05:14:09 CDT 2025