CODEWITHSUNDEEP
sqloracle-database
vkrishna
vkrishna

Reputation: 95

How to select column values based on condition in Oracle

I have a table from which I'm generating a report. I can pass parameter values to call another report which uses a query to populate data. Data should be changed based on the parameter we pass in which the column values are selected based on the parameter values

Here are my sample input and expected output based on parameters.

Input Table

id_val      name    flag_1   f1_val     flag_2   f2_val     flag_3   f3_val
1           USD      1          aa         0        gg            1    mm
2           CAD      0          bb         0        hh            0    nn
3           INR      1          cc         1        ii            0    oo
4           JPY      0          dd         1        jj            1    pp
5           EUR      1          ee         1        kk            1    qq
6           AUD      1          ff         1        ll            0    rr

Output based on Parameter values

if Paramerter = Flag1

name    flag   f_val
USD     1       aa
INR     1       cc
EUR     1       ee
AUD     1       ff

if Paramerter = Flag2

name    flag  f_val
INR     1      ii
JPY     1      jj
EUR     1      kk
AUD     1      ll

if Paramerter = Flag3

name    flag   f_val
USD     1       mm
JPY     1       pp
EUR     1       qq

Upvotes: 2

Views: 4893

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

I would use Boolean logic in WHERE clause :

SELECT t.name, 1 AS Flag, 
       (CASE WHEN Paramerter = 'Flag1' THEN flag_1
             WHEN Paramerter = 'Flag2' THEN flag_2
             WHEN Paramerter = 'Flag3' THEN flag_3 
        END) AS f_val
FROM table t
WHERE (Paramerter = 'Flag1' AND flag_1 = 1) OR
      (Paramerter = 'Flag2' AND flag_2 = 1) OR
      (Paramerter = 'Flag3' AND flag_3 = 1);

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

You might use decode easily and cleanly as :

select name,1 as flag,
            decode('&Parameter','Flag1',F1_val
                               ,'Flag2',F2_val
                               ,'Flag3',F3_val) as f_val
  from tab
 where decode('&Parameter','Flag1',Flag_1 
                          ,'Flag2',Flag_2
                          ,'Flag3',Flag_3) = 1;

assuming '&Parameter' is a string type substitution variable for which the values 'Flag1', 'Flag2' or 'Flag3' might be passed .

-- if '&Parameter' = 'Flag1'
NAME    FLAG    F_VAL
USD      1      aa
INR      1      cc
EUR      1      ee
AUD      1      ff

-- if '&Parameter' = 'Flag2'
NAME    FLAG    F_VAL
INR     1       ii
JPY     1       jj
EUR     1       kk
AUD     1       ll

-- if '&Parameter' = 'Flag3'
NAME    FLAG    F_VAL
USD     1       mm
JPY     1       pp
EUR     1       qq

Rextester Demo

Upvotes: 2

D-Shih
D-Shih

Reputation: 46219

You are looking for unpivot, I would use UNION ALL to and add a grp make it for each flag.

 SELECT t1.* FROM (
    SELECT name,flag_1 flag,'Flag1' grp
    FROM T
    UNION ALL
    SELECT name,flag_2,'Flag2' grp
    FROM T
    UNION ALL
    SELECT name,flag_3,'Flag3' grp
    FROM T
) t1
WHERE t1.grp = ? and t1.flag > 0

NOTE

? is your parameter input it can instead of 'Flag1','Flag2' ...

Here is a sample 

CREATE TABLE T(
   id_val int,
   name varchar(50),
   flag_1 int,
   flag_2 int,
   flag_3 int
);


insert into t values (1 ,'USD',1,0,1);
insert into t values (2 ,'CAD',0,0,0);
insert into t values (3 ,'INR',1,1,0);
insert into t values (4 ,'JPY',0,1,1);
insert into t values (5 ,'EUR',1,1,1);
insert into t values (6 ,'AUD',1,1,0);

Query 1:

SELECT t1.NAME,t1.FLAG
FROM (
    SELECT name,flag_1 flag,'Flag1' grp
    FROM T
    UNION ALL
    SELECT name,flag_2,'Flag2' grp
    FROM T
    UNION ALL
    SELECT name,flag_3,'Flag3' grp
    FROM T
) t1
WHERE t1.grp = 'Flag3' and t1.flag > 0

Results:

| NAME | FLAG |
|------|------|
|  USD |    1 |
|  JPY |    1 |
|  EUR |    1 |

Upvotes: 1

Related Questions