3 Replies Latest reply on May 7, 2013 3:59 AM by braighvarrt

    ExecuteSQL Between Dates filtering

    braighvarrt

      Title

      ExecuteSQL Between Dates filtering

      Post

           The following calculation (A) produces the total quantity of a specific product ordered but when I change the SQL statement per the second example (B) to filter for just orders between two global date fields my calculation field is blank. Can anyone advise on the correct way to write the SQL statement WHERE clause to give the desired result.

           (A)

           SumOrderQtyInPeriod = ExecuteSQL ("select sum(l.OrderQty) from Products p join OrderlineItem l on p. \"__pkProductItemID\" = l.\"_fkProductID\" where l.\"_fkProductID\" =? " ; ""; "" ;__pkProductItemID )

            

           (B)

           ExecuteSQL ("select sum(l.OrderQty) from Products p join OrderlineItem l on p. \"__pkProductItemID\" = l.\"_fkProductID\" where l.\"_fkProductID\" =? AND o.OrderDate Between ? AND ? " ; ""; "" ;__pkProductItemID; GlobalResources::z_OpeningStockDate; GlobalResources::z_ClosingStockDate )

        • 1. Re: ExecuteSQL Between Dates filtering
          philmodjunk

               ExecuteSQL ("

               SELECT sum(l.OrderQty) FROM Products p

               JOIN OrderlineItem l ON p. \"__pkProductItemID\" = l.\"_fkProductID\"

               WHERE l.\"_fkProductID\" =? AND o.OrderDate Between ? AND ? 

               " ; ""; "" ;__pkProductItemID; GlobalResources::z_OpeningStockDate; GlobalResources::z_ClosingStockDate )

               I don't see where any table in your database is identified as "o" for use in your SQL expression.

          • 2. Re: ExecuteSQL Between Dates filtering
            braighvarrt

                 Thank you for responding to my problem. The o in o.OrderDate is incorrect. Since my original post I have tried various configurations of the statement but without managing to achieve my aim, which is to get the total number for a specific product ordered between 2 global dates.

                  

                 Statement (A) works but only gives me the total number of a specific product ordered on all orders. When I modified the statement as per (B) to filter for orders between 2 specific dates I get a blank field.

                 Any help much appreciated.

                  

                 (A)

                 Select sum(l.OrderQty)

                 From Product p, OrderLineItem l, CustOrder c

                 Where p.\"__pkProductItemID\" = l.\"_fkProductID\" and l.\"_fkOrderID\" = c.\"__pkOrderID\"

                 AND l.\"_fkProductID\" = ?";

                 ""; "" ;__pkProductItemID)

                 (B)

                 ExecuteSQL ("

                 Select sum(l.OrderQty)

                 From Product p, OrderLineItem l, CustOrder c

                 Where p.\"__pkProductItemID\" = l.\"_fkProductID\" and l.\"_fkOrderID\" = c.\"__pkOrderID\"

                 AND l.\"_fkProductID\" = ?

                 AND c.OrderDate BETWEEN ? AND ?";

                 ""; "" ;__pkProductItemID; StockDateStart;StockDateEnd)

            • 3. Re: ExecuteSQL Between Dates filtering
              braighvarrt

                   Hi all,

                   I think I have resolved the matter. The fields StockDateStart and StockDateEnd are global fields. Making them non-global resolved the matter.

                   Thank you to all who took the time to look into the problem.