CODEWITHSUNDEEP
sqloracle-database
Nishant
Nishant

Reputation: 21934

How to select columns conditionally in Oracle?

I want to select a data from Oracle . However I have a column - CHILD_IDN - that decides which column is right for me . If the column is having an idn then I need to pick CHILD from the table else by default I should pick the PARENT . MSSQL allows a simple CASE statement to work . But what about Oracle ? Is there a Oracle vs MSSQL compatible query for this ? I think its a non - normalized data hence need advice .

For example

TableA

1    PARENT  CHILD_IDN CHILD
2    Okay 
3    Cool    1         PickMe1 
4    Fine
5    Test
6    Bar     2         Pickme2
7    Hello   


Now the result expected is 

Okay 
PickMe1
Fine
Test
Pickme2
Hellow

Upvotes: 1

Views: 2644

Answers (3)

ngrashia
ngrashia

Reputation: 9904

Use a CASE or DECODE statement as below:

Test Data

WITH TABLEA AS
(
SELECT 1 SLNO, 'OKAY' PARENT, '' CHILD_ID, '' CHILD FROM DUAL
UNION 
SELECT 2 SLNO, 'COOL' PARENT, '1' CHILD_ID, 'PICKME1' CHILD FROM DUAL
UNION 
SELECT 3 SLNO, 'FINE' PARENT, '' CHILD_ID, '' CHILD FROM DUAL
UNION 
SELECT 4 SLNO, 'TEST' PARENT, '' CHILD_ID, '' CHILD FROM DUAL
UNION 
SELECT 6 SLNO, 'BAR' PARENT, '2' CHILD_ID, 'PICKME2' CHILD FROM DUAL
UNION 
SELECT 7 SLNO, 'HELLO' PARENT, '' CHILD_ID, '' CHILD FROM DUAL
)

Alternative 1: with Decode

SELECT DECODE(CHILD_ID,'',PARENT,CHILD) CHILD
FROM TABLEA

Alternative 2: With Case

SELECT (CASE WHEN CHILD_ID IS NOT NULL THEN child 
       ELSE parent 
       END) value
FROM TABLEA

The above query with test data provides the expected result as asked in the question!

Upvotes: 1

Dba
Dba

Reputation: 6649

You can use CASE statement in Oracle too. like,

SELECT CASE WHEN child_idn IS NOT NULL THEN child 
       ELSE parent 
       END
FROM   tableA;

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1271131

You can do this with a case:

select (case when child_idn = 2 then child else parent
        end)
from table t;

This is standard SQL and should work in either database.

Note: this assumes that the "blank" values are NULL and uses the fact that the condition will fail on a NULL value. You can also be explicit if you want:

select (case when child_idn = 1 then parent
             when child_idn = 2 then child
             else parent
        end)

Upvotes: 2

Related Questions