if statement inside a select statement

[slechols]

I am trying to run a select statement that includes multiple if statements when populating a field. Is it possible to put an IF inside a SELECT clause? Here is the code. It says that FROM keyword not found where expected (line 7).

SELECT
m.niin,
d.ddr,
d.d200_mdr,
r.ddr,
r.rbl_mdr,
IF d.d200_mdr>r.rbl_mdr THEN
d.d200_mdr
ELSE IF
r.rbl_mdr>d.d200_mdr THEN
r.rbl_mdr
ELSE IF
r.rbl_mdr=d.d200_mdr THEN
d.d200_mdr
ELSE
'0'
END IF,
IF d.d200_mdr>r.rbl_mdr THEN
'D200A'
ELSE IF
r.rbl_mdr>d.d200_mdr THEN
'RBL'
ELSE
'D200A'
ELSE IF
r.rbl_mdr=d.d200_mdr THEN
'D200A'
ELSE
'USER'
END IF
FROM
RBL_MDR_VIEW r,
D200_MDR_VIEW d
MASTER_NIIN m
WHERE
r.niin=m.niin +
AND d.niin=m.niin +

 

[carp]

You can't use IF/ELSE in a SQL statement - those are PL/SQL words.
You might want to try using CASE instead.

 

[KULJEETSINGH]

use decode function
it works like if elsif else.

 

[sem]

You CAN use CASE clause in sql (though not in pl/sql) since 8.1.7 at least:

...
case
when d.d200_mdr>r.rbl_mdr THEN
d.d200_mdr
when r.rbl_mdr>d.d200_mdr THEN
r.rbl_mdr
...
ELSE
'0'
END case,
...

Regards, Dima

 

[lewisp]

Decode will work in this case too. Your first IF, for example, would convert from

[tt]IF d.d200_mdr>r.rbl_mdr THEN
d.d200_mdr
ELSE IF
r.rbl_mdr>d.d200_mdr THEN
r.rbl_mdr
ELSE IF
r.rbl_mdr=d.d200_mdr THEN
d.d200_mdr
ELSE
'0'
END IF[/tt]

to

[tt]SELECT Decode(Sign(d.d200_mdr - r.rbl_mdr),
1,d.d200_mdr,
0,d.d200_mdr,
-1,r.rbl_mdr,
0),
....[/tt]