I have a requirement such that, if one condition is true i should execute on query Q1 if that condition fails, i should execute another query Q2. This queries result is the records of search performed by the user. I am using case when statement for if condition, as Q1 and Q2 have more than one column to retrieve, I am getting ORA-00913: too many values . I came to know that case when cannot execute queries with more columns in retrieving data. Can anyone suggest how to achieve this type requirement.
I cannot give exact query, but can provide pseudo code
select case when c1='1' then select c1,c2,c3 from table1 else select c1,c2,c3 from table2 end from table1;
Here iam giving sample data.
C1 C2 C3 1 null 1 1 2 null
C1 C2 C3 1 4 1 1 3 5 2 9 null
When i run query you provided, the output will be as below.
select coalesce(table2.c1, table1.c1) c1, coalesce(table2.c2, table1.c2) c2, coalesce(table2.c3, table1.c3) c3 from table1 left outer join table2 on (your keys here) and table1.c1 <> '1' -- This gets table1 if c1 = '1';
C1 C2 C3 1 4 1 1 2 5 2 9 null
But what iam expecting the output is
C1 C2 C3 1 null 1 1 2 null 2 9 null
Hope i explained clearly.