13 Replies Latest reply on Nov 15, 2009 6:49 AM by BKBK

    Problem with SELECT statement. What is wrong with it?

    emartek1 Level 1

      Why is this query....

       

      <cfquery datasource="manna_premier" name="kit_report">
      SELECT Orders.ID,
             SaleDate,
             Orders.UserID,
          Distributor,
          DealerID,
          Variable,
          TerritoryManager,
          US_Dealers.ID,
             DealerName,
          DealerAddress,
          DealerCity,
          DealerState,
          DealerZIPCode,
          (SELECT SUM(Quantity)
           FROM ProductOrders PO
           WHERE PO.OrderID = Orders.ID) as totalProducts,    
      FROM Orders, US_Dealers
      WHERE US_Dealers.ID = DealerID AND SaleDate BETWEEN #CreateODBCDate(FORM.Start)# AND #CreateODBCDate(FORM.End)# AND Variable = '#Variable#'
      </cfquery>

       

      giving me this error message...

       

      Error Executing Database Query.

      [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.
      The error occurred in D:\Inetpub\mannapremier\kit_report2.cfm: line 20
      18 :              WHERE PO.OrderID = Orders.ID) as totalProducts,         
      19 : FROM Orders, US_Dealers
      20 : WHERE US_Dealers.ID = DealerID AND SaleDate BETWEEN #CreateODBCDate(FORM.Start)# AND #CreateODBCDate(FORM.End)# AND Variable = '#Variable#'
      21 : </cfquery>
      22 : 
      

      SQLSTATE  42000
      SQL   SELECT Orders.ID, SaleDate, Orders.UserID, Distributor, DealerID, Variable, TerritoryManager, US_Dealers.ID, DealerName, DealerAddress, DealerCity, DealerState, DealerZIPCode, (SELECT SUM(Quantity) FROM ProductOrders PO WHERE PO.OrderID = Orders.ID) as totalProducts, FROM Orders, US_Dealers WHERE US_Dealers.ID = DealerID AND SaleDate BETWEEN {d '2009-10-01'} AND {d '2009-10-31'} AND Variable = 'Chick Days pre-book'
      VENDORERRORCODE  -3504
      DATASOURCE  manna_premier
      Resources:


      I copied it from a different template where it works without error...

       

      <cfquery name="qZVPData" datasource="manna_premier">
      SELECT UserID,
             TMName,
          UserZone,
                (SELECT COUNT(*)
                 FROM Sales_Calls
                 WHERE Sales_Calls.UserID = u.UserID) as totalCalls,
          (SELECT COUNT(*)
           FROM Orders
           WHERE Orders.UserID = u.UserID) as totalOrders,
          (SELECT SUM(Quantity)
           FROM ProductOrders PO
           WHERE PO.UserID = u.UserID AND PO.NewExisting = 1) as newItems,
          (SELECT SUM(NewExisting)
           FROM  ProductOrders PO_
           WHERE PO_.UserID = u.UserID) as totalNew,
          SUM(totalOrders)/(totalCalls) AS closePerc
      FROM Users u
      WHERE UserZone = 'Central'
      GROUP BY UserZone, UserID, TMName
      </cfquery>

       

      What is the problem?