4 Replies Latest reply on Jan 13, 2014 10:03 PM by user19752

    Nested SQL

    Vyke

      I have 2 tables

       

      Cart (CartID)

      LineItems (CartID, LineItemID, Summary, MaterialID)

       

      I am trying to pull only the LineItemID from the LineItems where the summary is a distinct value to use for filtering a portal for duplicates.

       

       

      ExecuteSQL(
      
      
      "SELECT
       I.LineItemsID
      FROM
       LineItems AS I
      WHERE
       I.LineItemsID IN (SELECT DISTINCT I.c_SumOfValues FROM I WHERE FK_CartID =?)";
      "";"";Cart::CartID
      )
      
      

       

      but nothing I try seems to work, I just keep getting "?" Any help would be appreciated.

        • 1. Re: Nested SQL
          erolst

          Have you tried without using aliases? This here works for me:

           

          ExecuteSQL ( "

          SELECT

                    T.\"_kp_transactionID\"

          FROM

                    PTO__Transactions AS T

          WHERE

                    T.amount IN ( SELECT DISTINCT ( amount ) FROM PTO__Transactions  WHERE \"_kf_vorgangID\" = ?)

          " ; "" ; "" ; PTO__Transactions::_kf_vorgangID

          )

          … but using T within the second SELECT also only yields a ?.

           

          I think the inner SELECT opens a new scope in which your previously defined aliases are not recognized.

          • 2. Re: Nested SQL
            Vyke

            I tried it the way you suggest, and you are correct, the alias does not work withing the subquery, no alias is working in there even when defined within the subquery. I no longer get an error from the query but

             

             

             

            ExecuteSQL(

            "SELECT

                      I.PK_LineItemsID

            FROM

                      SCLI___Cart I

            WHERE

                      I.c_SumOfValues IN (SELECT DISTINCT (c_SumOfValues) FROM SCLI___Cart  WHERE FK_cartID = ?)";"";"";Cart::PK_CartID)

             

            Just returns me the entire list of key in the table.

            • 3. Re: Nested SQL
              wimdecorte

              why not brreak it down in a Let statement to do two queries instead of one nested one?  Should be a lot easier to troubleshoot

              • 4. Re: Nested SQL
                user19752

                I can't mean what is DISTINCTed SumOfValues, but

                 

                "SELECT

                          I.PK_LineItemsID

                FROM

                          SCLI___Cart I

                WHERE

                          I.PK_LineItemsID IN (SELECT MAX(PK_LineItemsID) FROM SCLI___Cart WHERE FK_cartID = ? GROUP BY c_SumOfValues)

                 

                MAX can be replaced with MIN for your purpose.

                 

                And may be same as simply

                SELECT MAX(PK_LineItemsID) FROM SCLI___Cart WHERE FK_cartID = ? GROUP BY c_SumOfValues