How to write following SQL query in Qlikview

[#1]

Hello All,

I am trying to write SQL query with following format in Qlikview

SQL SELECT columname as CL1,

                     columname as CL2,

                     (Select Case when condition then action

                                 Case when condition then action

                                end

                       from table1,table2 where table1.col=table2.col and table1.col2='xyz' )    as CL3,

                     (Select Case when condition then action

                                 Case when condition then action

                                   end

                      from table1,table2 where table1.col=table2.col and table1.col2='xyz' )    as CL4,

                       colomnname as CL5

      FROM table

    where condition;

Query working in SQL but it is not working in Qlikvew.

It is giving me following error

ORA-01427: single-row subquery returns more than one row"

I googled this error but still couldnt figure out how to write above query in Qlikview.

Thank you,

Regards,

Rohit.

[Gysbert_Wassenaar]

Qlikview's not the problem here. You're using subqueries as columns and one of them is returning more than one row. That simply cannot work. You need to make sure your subqueries return exactly one row. If you run the same sql statement in a tool like Toad or SQL Developer you would get the same error.

[#3]

What I have found to be easiest is to do this work in sql by creating a view to get the data the way you want it and then just bring the data in from the view using a simple select procedure.  Hope this helps!

[#4]

Thank you both of you for Reply

Thnks a lot