4 Replies Latest reply: Jan 22, 2014 3:52 PM by Gysbert Wassenaar RSS

    More fun with WHERE.... IN....

    Robert Fishel

      Hello fellow QlikView enthusiasts.  I have a SQL statement that I am really struggling with.  Would someone out there please re-write this exactly how it should be? 

       

      SELECT sourcingclass_1.[AX PO#], sourcingclass_1.AX_Class, "Multiple_classes" AS Class_dup FROM sourcingclass_1 WHERE (((sourcingclass_1.[AX PO#]) In (SELECT [AX PO#] FROM [sourcingclass_1] As Tmp GROUP BY [AX PO#] HAVING Count(*)>1 ))) ORDER BY sourcingclass_1.[AX PO#];

       

      I think I should be writing this as a WHERE EXISTS, but would really appreciate a clearly written final answer for myself and others as a reference.  Thank you.

        • Re: More fun with WHERE.... IN....
          Angad Singh

          You can try below queries:

           

          SELECT sourcingclass_1.[AX PO#], sourcingclass_1.AX_Class, "Multiple_classes" AS Class_dup

          FROM sourcingclass_1

          join (SELECT [AX PO#] FROM sourcingclass_1 GROUP BY [AX PO#] HAVING Count(*)>1) test on sourcingclass_1.[AX PO#] = test.[AX PO#]

          ORDER BY sourcingclass_1.[AX PO#];

           

          or you can use below

           

           

          SELECT sourcingclass_1.[AX PO#], sourcingclass_1.AX_Class, "Multiple_classes" AS Class_dup

          FROM sourcingclass_1

          WHERE sourcingclass_1.[AX PO#] In (SELECT [AX PO#] FROM sourcingclass_1 GROUP BY [AX PO#] HAVING Count(*)>1)

          ORDER BY sourcingclass_1.[AX PO#];

           

          The first one will be better since we are using join clause which is more performance oriented than Where clause.

           

          Thanks,

          Angad

          • Re: More fun with WHERE.... IN....
            Gysbert Wassenaar

            You can simply use your sql statement to load the data you want into qlikview. Qlikview will simply send the sql statement to the dbms (or oledb/odbc driver) for execution and will load the records that are returned.

             

            You could also try this, which will be less efficient:

             

            Temp:

            LOAD [AX PO#], if(exists([AX PO#]), [AX PO#]) as [AX PO#_2];

            SELECT [AX PO#] FROM sourcingclass_1;

             

            Result:

            LOAD * WHERE exists([AX PO#_2],sourcingclass_1.[AX PO#]);

            SELECT sourcingclass_1.[AX PO#], sourcingclass_1.AX_Class, "Multiple_classes" AS Class_dup FROM sourcingclass_1;

             

            DROP table Temp;