SQl Question

[ADB1]

Could somebody please tell me the syntax/format for using an IF THEN ELSE statement in Oracle?

Thanks,

Adam.

 

[BJCooperIT]

[tt]
if VAR1 = VAR2 then
null; -- processing
elsif VAR3 = VAR4 then
null; -- processing
else null; -- processing
end if;
[/tt]


(select * from life where brain is not null)
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[ADB1]

Thanks for the information.

I was trying to enter the following sql, and i keep getting the error 'Missing Right Parenthesis'
---------------------------------------------
SELECT
IF (ZEUS.SPEC_ORD.SPEC_ORD_STAT = 'EEA' )
THEN (ZEUS.SPEC_ORD.SPEC_ORD_STAT_CRBD - ZEUS.SPEC_ORD_STAT_HIST.STAT_CHANG_DATE)
ELSE NULL END

FROM
ZEUS.SPEC_ORD,
ZEUS.SPEC_ORD_STAT_HIST
WHERE
( ZEUS.SPEC_ORD.SPEC_ORD_NUMB = ZEUS.SPEC_ORD_STAT_HIST.SPEC_ORD_NUMB )
-----------------------------------------------

Thanks,

Adam.

 

[lewisp]

In pure SQL you need a decode:

SELECT
Decode(ZEUS.SPEC_ORD.SPEC_ORD_STAT,
'EEA',ZEUS.SPEC_ORD.SPEC_ORD_STAT_CRBD - ZEUS.SPEC_ORD_STAT_HIST.STAT_CHANG_DATE,
NULL)
FROM
ZEUS.SPEC_ORD,
ZEUS.SPEC_ORD_STAT_HIST
WHERE
( ZEUS.SPEC_ORD.SPEC_ORD_NUMB = ZEUS.SPEC_ORD_STAT_HIST.SPEC_ORD_NUMB )

 

[sem]

You may use CASE statement. The syntax is:

CASE
WHEN <condition1> THEN <result1>
WHEN <condition2> THEN <result2>
...
ELSE <resultN>
END

Regards, Dima