if statement within select statement

[DirtyB]

I'm fairly new to Oracle. Can I put an "if" statement within my "select" statement to determine what value i'm returning?


for example:

select
field 1,
field 2,
field 3,
if field 4 in (select field 1 from TABLE 2)
(here I want to return string "Yes&quot
else
(here I want to return string "No&quot
end if,
field 5
from Table 1


Basically, I need to return a Yes if this record's ID exists in another table. I thought using an if statement within my select statement would be a way to do it, but I can't figure out the syntax.

Thanks

 

[sfvb]

There's probably quite a few ways to accomplish this. Here's one:

SELECT   field_1, field_2, field_3,
         NVL(  (SELECT   distinct 'Yes' 
                FROM     Table_2  
                WHERE    Table_2.field_1 = Table_1.field_4),
             'No') Yes_No, field_5
FROM     Table_1

 

[DirtyB]

SFVB:

Thanks!! I think this is a good solution to solve my current problem.

But for future reference, in Oracle can you put an "if" statement within a select statement?

 

[jimbopalmer]

the closest to an if in SQL is decode(), for a real if you need PL/SQL and it still is not inside the SQL I tried to remain child-like, all I acheived was childish.

 

[Turkbear]

HI,
No, SqlPlus does not recognize IF as a valid operator.
( It can be used in PL/Sql code )


PS
goto

http://tahiti.oracle.com

and look into the many commands and options you CAN use in SqlPLus..

 

[DirtyB]

Thanks everyone....Turkbear...i'll check that out.

 

[jee]

Actually new as of 8.1.6 (I think) there is something like an IF that can be used within a Select and that is a CASE expression. Here is an example of a statement that uses it:

The following statement finds the average salary of all employees in the EMP table. If an employee's salary is less than $2000, the CASE expression uses $2000 instead.

SELECT AVG(CASE WHEN e.sal > 2000 THEN e.sal ELSE 2000 END) from emp e;

In 8i there are limitations, such as you can't use this within PL/SQL unless you use Dynamic SQL. I think this problem is fixed in 9i.