PL/SQL...if inside a select?

Question

I'm pretty new when it comes to PL/SQL, and I have to modify someone else's stored procedure.

Is it possible to add an if statement in the middle of a select? The procedure I have tries to open a cursor and select a bunch of stuff from different tables into it (don't ask me why they didn't use a join) and return the cursor. The thing I'm trying to do is, there are two columns with similar data that can never both be populated in the same time. So if one is null, the other should have a value, and the cursor needs the value from whichever is populated. So... if statement inside the select?

I won't post the actual code, cause it'll make your eyeballs bleed, but it looks something like...

open rc for
select l.data1 as ld1, l.data2 as ld2, b.data1 as bd1, 
       b.data2 as bd2, c.data1 as as c_d1, c.data2 as cd2
from tablel l, tableb b, tablec c
where blahblahblah
and c.data1 = [b.data3 if b.data4 is null, else b.data4]?

I can't seem to get the syntax, if it's possible.

Answer 1

You don't need an IF, you can do it with an OR.

SELECT
    l.data1 AS ld1, 
    l.data2 AS ld2, 
    b.data1 AS bd1, 
    b.data2 AS bd2, 
    c.data1 AS c_d1, 
    c.data2 AS cd2
FROM
    tablel l, 
    tableb b,
    tablec c
WHERE
    ... blahblahblah ...
AND (
      ( b.data4 IS NULL AND c.data1 = b.data3 ) OR ( c.data1 = b.data4 )
    )

Answer 2

Use a case statement instead?

and c.data1 = CASE WHEN b.data3 IS NOT NULL THEN b.data3 ELSE b.data4 END

Answer 3

You can use NVL or COALESCE for that:

open rc for
select l.data1 as ld1, l.data2 as ld2, b.data1 as bd1, b.data2 as bd2, c.data1 as as     c_d1, c.data2 as cd2
from tablel l, tableb b, tablec c
where blahblahblah
and c.data1 = NVL(b.data4,b.data3)

Answer 4

Use decode or case, depending on your version of Oracle.

For example:

and c.data1 = case bdata4 when null then bdata3 else bdata4 end

Decode is similar, but use case unless you're forced to use decode, as it's easier to read.

Examples

Answer 5

Sounds like you need a case statement in the select section?