Select statement And If/else statement

Question

Sorry I am new in PL/SQL toad for oracle. I have a simple problem. I need to find some column name in this table "JTF_RS_DEFRESOURCES_VL" and i do that with this script. just an example column.

SELECT column1, 
       column2, 
       column3, 
       column4, 
       column5, 
       end_date_active 
FROM   jtf_rs_defresources_vl

Then i want to use "if else statement" that if END_DATE_ACTIVE = null then it is active else it is inactive.

http://stackoverflow.com/a/9581790/139010 – Matt Ball Jan 24 '14 at 06:24 — Matt Ball, Jan 24 '14 at 06:24

score 2 · Accepted Answer · answered Jan 24 '14 at 06:23

2

It sounds like you want a CASE statement

SELECT (CASE WHEN end_date_active IS NULL 
             THEN 'Active'
             ELSE 'Inactive'
         END) status,
       <<other columns>>
  FROM jtf_rs_defresources_vl

answered Jan 24 '14 at 06:23

Justin Cave

227,342
24
367
384

thank you so much sir. can you explain what is the use of "case"? – Never Stop Learning Jan 24 '14 at 06:27
1

case statement will compares an expression – Nagaraj S Jan 24 '14 at 06:29
1

@DontStopLearning - `CASE` is the way you implement IF/THEN logic in SQL. `IF` statements are valid in PL/SQL but not in SQL. – Justin Cave Jan 24 '14 at 06:32

score 1 · Answer 2 · answered Jan 24 '14 at 06:47

You can use a CASE expression like @Justin suggested, but in Oracle there's a simpler function - NVL2. It receives three arguments and evaluates the first. If it isn't null the second argument is returned, if it is, the third one is returned.

So in your case:

SELECT NVL2(end_date_active, 'Active', 'Inactive') AS status,
       <<other columns>>
  FROM jtf_rs_defresources_vl

score 0 · Answer 3 · answered Jan 24 '14 at 14:50

0

select case end_date_active 

   when is null then 'ACTIVE',

   else 'INACTIVE'

   end as 'STATUS'

from jtf_rs_defresources_vl ;

answered Jan 24 '14 at 14:50

redsoxlost

1,215
5
19
32

1

You should developed your answer instead of posting one unexplained. – Maxime Lorant Jan 24 '14 at 15:10

Select statement And If/else statement

3 Answers3