7 Replies Latest reply: May 24, 2016 6:07 AM by kushal chawda RSS

    Nested Select

    Morne Schoeman

      Hi,

       

      Please help me with the following.

       

      In need to select values from SAP table based on field values from another SAP table, i.e. select from select

       

      Script part underneath in bold is the part I am needing assistance with?

       

      //////////////////////////////////////////////////////////

      [VBAK]: //Sales Document Header Data

      LOAD VBELN AS [Sales Document],

      ERNAM AS [Created by],

      AUDAT AS [Document Date],

      KNUMV AS [Doc. condition no.],

      where

      AUDAT > '20160501'

       

      LEFT JOIN ([VBAK])

      LOAD KNUMV AS [Doc. condition no.],

      KSCHL AS [Condition type],

      KBETR AS [Condition rate]

      ;

      SQL SELECT

      KNUMV,

      KSCHL,

      KBETR

      FROM KONV

      WHERE

      KNUMV =

      (Load [Doc. condition no.],

      Resident VBAK);

      //////////////////////////////////////////////////////////

       

      Field KNUMV is an indexed field, if I only use a left join the query runs for hours instead of seconds.

       

      Thanks in advance

        • Re: Nested Select
          Tamil Nagaraj

          Hi Mome,

           

          Try this,

           

          //////////////////////////////////////////////////////////

          [VBAK]: //Sales Document Header Data

          LOAD VBELN AS [Sales Document],

          ERNAM AS [Created by],

          AUDAT AS [Document Date],

          KNUMV AS [Doc. condition no.],

          where

          AUDAT > '20160501'

           

          LEFT JOIN ([VBAK])

          LOAD KNUMV AS [Doc. condition no.],

          KSCHL AS [Condition type],

          KBETR AS [Condition rate];

           

          Temp:

          Load Concat(Distinct Chr(39) & [Doc. condition no.] & Chr(39),', ') as List resident VBAK;

          LET vList = Peek('List', 0, 'Temp');

          Drop table Temp; 


          SQL SELECT

          KNUMV,

          KSCHL,

          KBETR

          FROM KONV

          WHERE

          Match(KNUMV, $(vList));

          //////////////////////////////////////////////////////////

          • Re: Nested Select
            kushal chawda

            try this

             

            [VBAK]: //Sales Document Header Data

            LOAD VBELN AS [Sales Document],

            ERNAM AS [Created by],

            AUDAT AS [Document Date],

            KNUMV AS [Doc. condition no.],

            where

            AUDAT > '20160501';

             

            Temp:

            Load Distinct Concat(Chr(39) & [Doc. condition no.] & Chr(39),', ') as List

            resident VBAK;


            LET vList = Peek('List', 0, 'Temp');

            Drop table Temp;


            LEFT JOIN ([VBAK])

            LOAD KNUMV AS [Doc. condition no.],

            KSCHL AS [Condition type],

            KBETR AS [Condition rate];

             

            SQL SELECT

            KNUMV,

            KSCHL,

            KBETR

            FROM KONV

            WHERE

            Match(KNUMV, $(vList));


              • Re: Nested Select
                Morne Schoeman

                Hi Tamil & Kushal, thank you for the feedback,

                 

                I see you have similar solutions,

                 

                I have tried both in the exact format you send them, but I get the same script error:

                 

                i.e.

                ////////////////////////////////////////////

                /QTQVC/OPEN_STREAM failed after 00:00:00 Key = SQL_ERROR (ID:00 Type:E Number:001 "(" is invalid here (due to grammar). contains an invalid character or it is a keyword. (It might be possible to escape it using "!"). Or a space is missing or there is one space too many.)

                SQL SELECT

                KNUMV,

                KSCHL,

                KBETR

                FROM KONV

                WHERE

                Match (KNUMV, '0117978213', '0117978214', '0117979191')

                ////////////////////////////////////////////


                Am I making an obvious syntax error or may it be that SAP Connector doesn't recognize this command?


                Regards


                  • Re: Nested Select
                    Morne Schoeman

                    I have replaced the statement Load Distinct Concat(Chr(39) & [Doc. condition no.] & Chr(39),', ') as List

                    with

                    Load Distinct Concat('KNUMV = ' & Chr(39) & [Doc. condition no.] & Chr(39),' or ') as List

                     

                    Works perfectly!

                     

                    Thanks again

                      • Re: Nested Select
                        Tamil Nagaraj

                        Morne,

                         

                        Ah!! I forgot to see the SQL statement. Match is a Qlikview function and it won't work in Sql script. Glad that you found the solution!


                        You can try like below


                        Where KNUMV IN ($(vList));

                        • Re: Nested Select
                          kushal chawda

                          try this instead

                           

                          [VBAK]: //Sales Document Header Data

                          LOAD VBELN AS [Sales Document],

                          ERNAM AS [Created by],

                          AUDAT AS [Document Date],

                          KNUMV AS [Doc. condition no.],

                          where

                          AUDAT > '20160501';

                           

                          Temp:

                          Load Distinct Concat(Chr(39) & [Doc. condition no.] & Chr(39),', ') as List

                          resident VBAK;


                          LET vList = Peek('List', 0, 'Temp');

                          Drop table Temp;


                          LEFT JOIN ([VBAK])

                          LOAD KNUMV AS [Doc. condition no.],

                          KSCHL AS [Condition type],

                          KBETR AS [Condition rate];

                           

                          SQL SELECT

                          KNUMV,

                          KSCHL,

                          KBETR

                          FROM KONV

                          WHERE KNUMV in ($(vList))