CODEWITHSUNDEEP
sqloracle-databasenullcase
Never Stop Learning
Never Stop Learning

Reputation: 785

Select statement And If/else statement

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.

Upvotes: 2

Views: 500

Answers (3)

redsoxlost
redsoxlost

Reputation: 1235

select case end_date_active 

   when is null then 'ACTIVE',

   else 'INACTIVE'

   end as 'STATUS'

from jtf_rs_defresources_vl ;

Upvotes: 0

Mureinik
Mureinik

Reputation: 311573

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

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231711

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

Upvotes: 2

Related Questions