CODEWITHSUNDEEP
sqloracle-database
Pleasant94
Pleasant94

Reputation: 501

Select different query based on a variable

I want to execute a different query based on the value of a variable. I have a table with a lot of columns, and I want to select and rename a different numbers of fields based on a variable.

Something like

var varbl VARCHAR2

exec :varbl := 'XX'


if :varbl = 'XX'
    (select a, b, c from table1 where cond)
elsif :varbl = 'XXXX'
    (select d, e, f, g from table1 where cond)
end

I was trying to achieve this trough

decode( :varbl, 'XX',
            (QUERY 1),
            (decode( :varbl, 'XXX',
                        (QUERY 2), default)),
            default)

but it doesn't work, because decode should be used in a SELECT clause and i loose the name of the columns. furthermore, I get 'too many values' as error if I select more than a column in the inner queries.

I'm working on Oracle SQL.

This answer doesn't work for me, because I have at least 30 fields to differentiate, in 3 different cases. I just want some solution that allows me to execute a different query.

Upvotes: 0

Views: 258

Answers (1)

ekochergin
ekochergin

Reputation: 4129

For this case to work without plsql, you may try execute both of the queries checking the variable's value in where-part. I dod not test this example, but the idea must be clear to you

select a, b, c 
  from table1 
 where :varbl = 'XX'
   and cond;

select d, e, f, g 
  from table1 
 where :varbl = 'XXXX'
   and cond;

So, the last query will return nothing whereas first query will get you all the lines you need for 'XX' value.

Upvotes: 1

Related Questions