CODEWITHSUNDEEP
sqloracle-database
user2647888
user2647888

Reputation: 721

How to do the below select inside case statement in oracle

I want to do the following.

If COLUMN_A <> COLUMN_B then print COLUMN_A,COLUMN_B,and another dummy column status as not ok. I tried the following.

SELECT CASE WHEN COLUMN_A <> COLUMN_B 
THEN SELECT COLUMN_A, COLUMN_B,status as 'NOT OK'--(im not sure what to do here)
 END
FROM TABLE_1

Hi I'm sorry i couldn't express the question properly.

Here is what I'm expecting.

ROW   COLUMN_A      COLUMN_B    STATUS
1     100                        OK
2     250           350          NOT OK
3     300           NULL         NULL COLUMN_A
4     NULL          400          NULL COLUMN_B

Based on the below condition .

If COLUMN_A = COLUMN_B (THEN DISPLAY ROW1 Output)
COLUMN_A != COLUMN_B (THEN DISPLAY ROW2 oUTPUT)
IF COLUMN_B IS NULL THEN DISPLAY ROW3 Output 
IF COLUMN_A IS NULL THEN DISPLAY ROW4 Output

Upvotes: 0

Views: 5236

Answers (2)

Vinnie
Vinnie

Reputation: 3929

Try selecting the columns and using the case to evaluate each row:

SELECT COLUMN_A, COLUMN_B
, CASE WHEN COLUMN_A <> COLUMN_B THEN 'Not OK' ELSE 'OK' END AS [Status]
FROM Table1

Per your comments, you can have multiple cases within a single case statement: * Update - While not elegant, you can handle each necessary case with it's own statement. Since you want to determine what column is null, you will want to evaluate that first, then perform the not equals comparison.

SELECT COLUMN_A, COLUMN_B, COLUMN_C, COLUMN_D
, CASE WHEN COLUMN_A IS NULL THEN 'A is NULL'
WHEN COLUMN_B IS NULL THEN 'B is NULL'
WHEN COLUMN_C IS NULL THEN 'C is NULL'
WHEN COLUMN_C IS NULL THEN 'D is NULL'
WHEN COLUMN_A <> COLUMN_B THEN 'Not OK' 
WHEN COLUMN_A <> COLUMN_B THEN 'Not OK AB' 
WHEN COLUMN_B <> COLUMN_C THEN 'Not OK BC' 
WHEN COLUMN_C <> COLUMN_D THEN 'Not OK CD'

ELSE 'OK' END AS [Status]
FROM Table1

However, if you only want three columns, a union or union all would be a reasonable way to go.

Upvotes: 1

Frank Schmitt
Frank Schmitt

Reputation: 30765

If you want to get a different output for NULL values, you'll have to handle them explicitly:

with table1(column_a, column_b) as (
  select 1, 2 from dual union all
  select 2, 2 from dual union all
  select 3, null from dual union all
  select null, 4 from dual union all
  select null, null from dual
)
SELECT 
  COLUMN_A, 
  COLUMN_B,
  CASE 
    WHEN COLUMN_A <> COLUMN_B THEN 'Not OK' 
    WHEN column_a is null and column_b is null then 'both NULL'
    WHEN column_a is null then 'A null'
    WHEN column_b is null then 'B null'
ELSE 'OK' END AS Status
FROM Table1

Upvotes: 0

Related Questions