Results 1 to 13 of 13

Thread: SQL Join Error

  1. #1
    Join Date
    Feb 2010
    Posts
    21

    Unanswered: SQL Join Error

    Hello All,

    I am getting a bit stuck on the following sql string, I have an asp classic web page connected to an access 2007 database. I am trying to get the totals from a query called qrycondetailsextended totals, the fields I want from there are sumofextended price, perhaps sumofquantity too but most importantly for now is the sumofextended price field.

    Here is my code:

    strSQL = "SELECT tbl_ShipOrders.ship_order_id, tbl_ShipOrders.idate, tbl_ShipOrders.invamt, tbl_ShipOrders.receiver_id, tbl_ShipOrders.truck_id, " & _
    "tbl_ShipOrders.ship_order_id, tbl_ShipOrders.sender_id, tbl_ShipOrders.provcred, tbl_shiporders.loaddate, " & _
    "tbl_ShipOrders.loaddate, tbl_ShipOrders.Payment_id, tbl_truck_Details.truckid, qrycondetailsextendedtotals.sumofquantity" & _
    "tbl_ShipOrders.del, tbl_ShipOrders.discountpr, Total.sumofquantity AS Total, Sender.company AS Sender_name, Sender.Tel_1 AS Sender_Tel, Sender.Tel_2 AS Sender_Tel2," & _
    "Receiver.company AS Receiver_name, Receiver.Tel_1 AS Receiver_Tel, Receiver.Tel_2 AS Receiver_Tel2, Truck.Truck_Registration AS TruckReg " & _
    "FROM (tbl_ShipOrders INNER JOIN tbl_Customers AS Sender " & _
    "ON tbl_ShipOrders.Sender_ID = Sender.custid) " & _
    "INNER JOIN tbl_Customers AS Receiver " & _
    "ON tbl_ShipOrders.Receiver_ID = Receiver.custid " & _
    "(tbl_shipOrders INNER JOIN qrycondetailsextendedtotals AS Total) " & _
    "ON tbl_shipOrders.ship_order_ID = Total.ship_ID " & _
    "WHERE tbl_ShipOrders.Loaddate Is Not Null " & _
    "AND tbl_ShipOrders.Del=0 " & _
    "AND tbl_ShipOrders.Payment_ID=4 " & _
    "AND tbl_ShipOrders.Branch_ID = " & Branchid


    I get this error:

    "Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

    [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'tbl_ShipOrders.Receiver_ID = Receiver.custid tbl_shipOrders INNER JOIN qrycondetailsextendedtotals AS Total ON tbl_shipOrders.ship_order_ID = Total.ship_I'."

    Using just:

    strSQL = "SELECT tbl_ShipOrders.idate, tbl_ShipOrders.invamt, tbl_ShipOrders.receiver_id, tbl_ShipOrders.truck_id, " & _
    "tbl_ShipOrders.ship_order_id, tbl_ShipOrders.sender_id, tbl_ShipOrders.provcred, tbl_shiporders.loaddate, " & _
    "tbl_ShipOrders.loaddate, tbl_ShipOrders.Payment_id, " & _
    "tbl_ShipOrders.del, tbl_ShipOrders.discountpr, Sender.company AS Sender_name, Sender.Tel_1 AS Sender_Tel, Sender.Tel_2 AS Sender_Tel2," & _
    "Receiver.company AS Receiver_name, Receiver.Tel_1 AS Receiver_Tel, Receiver.Tel_2 AS Receiver_Tel2 " & _
    "FROM (tbl_ShipOrders INNER JOIN tbl_Customers AS Sender " & _
    "ON tbl_ShipOrders.Sender_ID = Sender.custid) " & _
    "INNER JOIN tbl_Customers AS Receiver " & _
    "ON tbl_ShipOrders.Receiver_ID = Receiver.custid " & _
    "WHERE tbl_ShipOrders.Loaddate Is Not Null " & _
    "AND tbl_ShipOrders.Del=0 " & _
    "AND tbl_ShipOrders.Payment_ID=4 " & _
    "AND tbl_ShipOrders.Branch_ID = " & Branchid


    no problems, pulls the details out nicely, the problem is trying to create a further join from the qrycondetailsextendedtotals.

    Any and all help will be fully appreciated

    Thanks in Advance

    Regards

    Ben

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT ...
      FROM ((
           tbl_ShipOrders 
    INNER 
      JOIN tbl_Customers AS Sender 
        ON Sender.custid = tbl_ShipOrders.Sender_ID 
           ) 
    INNER 
      JOIN tbl_Customers AS Receiver 
        ON Receiver.custid = tbl_ShipOrders.Receiver_ID
           )
    INNER
      JOIN qrycondetailsextendedtotals AS Total
        ON Total.ship_ID = tbl_shipOrders.ship_order_ID
     WHERE ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2010
    Posts
    21
    deleted deleted deleted deleted deleted deleted deleted deleted deleted deleted
    Last edited by ben2203; 02-21-10 at 09:20. Reason: deleted

  4. #4
    Join Date
    Feb 2010
    Posts
    21
    Actually, I got this error:

    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

    [Microsoft][ODBC Microsoft Access Driver] Undefined function 'Nz' in expression.

    /delivery.asp, line 91

    with this code

    strSQL = "SELECT tbl_ShipOrders.idate, tbl_ShipOrders.invamt, tbl_ShipOrders.receiver_id, tbl_ShipOrders.truck_id, " & _
    "tbl_ShipOrders.ship_order_id, tbl_ShipOrders.sender_id, tbl_ShipOrders.provcred, tbl_shiporders.loaddate, " & _
    "tbl_ShipOrders.loaddate, tbl_ShipOrders.Payment_id, Total.sumofquantity AS Total, qrycondetailsextendedtotals.sumofquantity, " & _
    "tbl_ShipOrders.del, tbl_ShipOrders.discountpr, Sender.company AS Sender_name, Sender.Tel_1 AS Sender_Tel, Sender.Tel_2 AS Sender_Tel2," & _
    "Receiver.company AS Receiver_name, Receiver.Tel_1 AS Receiver_Tel, Receiver.Tel_2 AS Receiver_Tel2 " & _
    "FROM ((tbl_ShipOrders " & _
    "INNER JOIN tbl_Customers AS Sender " & _
    "ON Sender.custid = tbl_ShipOrders.Sender_ID) " & _
    "INNER JOIN tbl_Customers AS Receiver " & _
    "ON Receiver.custid = tbl_ShipOrders.Receiver_ID) " & _
    "INNER JOIN qrycondetailsextendedtotals AS Total " & _
    "ON Total.ship_ID = tbl_shipOrders.ship_order_ID " & _
    "WHERE tbl_ShipOrders.Loaddate Is Not Null " & _
    "AND tbl_ShipOrders.Del=0 " & _
    "AND tbl_ShipOrders.Payment_ID=4 " & _
    "AND tbl_ShipOrders.Branch_ID = " & Branchid

  5. #5
    Join Date
    Feb 2010
    Posts
    21
    Sorry, I had previously put in a syntax error, I meant to say thanks very much for your reply and I got this error, I researched it and it seems to be asp classic and access related, I tried using IFNULL in front of the query values but got nowhere, any ideas??

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the problem may lie in qrycondetailsextendedtotals

    which of course i can't see
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2010
    Posts
    21
    I researched it and says something to do with null values

    Qrycondetailsextendedtotals is a query based on another query called qrycondetailsextended, here is the sql:

    SELECT QryConDetailsExtended.Ship_ID AS Ship_ID, Sum(QryConDetailsExtended.[Extended Price]) AS [SumOfExtended Price], Sum(QryConDetailsExtended.Quantity) AS SumOfQuantity, Sum(QryConDetailsExtended.Discount) AS SumOfDiscount
    FROM QryConDetailsExtended
    GROUP BY QryConDetailsExtended.Ship_ID;


    the qrycondetailsextended sql is:

    SELECT tbl_consignment_details.*, CCur(Nz([Quantity],0)*Nz([Shipment_price],0)-Nz([Discount],0)) AS [Extended Price], tbl_Products.Shipping_Price
    FROM tbl_Products INNER JOIN tbl_consignment_details ON tbl_Products.ProductID = tbl_consignment_details.Product_ID;

    All based on the northwind sample dbase

  8. #8
    Join Date
    Feb 2010
    Posts
    21
    About your book, the pdf version is as complete as the book version, right? probably a silly question but thought I'd clarify it, I am thinking of purchasing it as a reference aid, it would be nice to have the book but I've just purchased a tablet pc so I might start using that put lots of ebooks in there since I am on the road a lot.

    You certainly taught me a great deal in a very short amount of time so I think it would be a great investment
    Last edited by ben2203; 02-21-10 at 10:36.

  9. #9
    Join Date
    Feb 2010
    Posts
    21
    Got it, thank you so much for your help, please let me know about this book as I am really eager to start reading it!!!

    You are a star!!!!

  10. #10
    Join Date
    Sep 2006
    Posts
    265
    Here's a tip.

    Don't worry about how the Sql statement looks, one single string will do. What I do is put the Query into Access first and all the ASP is reads the query and applies the WHERE part of the SQL statement.

    Doing this way you can get the SQL statement working in Access and later back it into Access if it ever goes wrong.

    I used to do it your way but now that I using ASP.Net, the SQL statement is one continuous statement. Now its a doddle to test the SQL statement.

    Simon

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ben2203 View Post
    About your book, the pdf version is as complete as the book version, right?
    yes it is, you get everything except the paper
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2010
    Posts
    21
    Quote Originally Posted by SimonMT View Post
    Here's a tip.

    Don't worry about how the Sql statement looks, one single string will do. What I do is put the Query into Access first and all the ASP is reads the query and applies the WHERE part of the SQL statement.

    Doing this way you can get the SQL statement working in Access and later back it into Access if it ever goes wrong.

    I used to do it your way but now that I using ASP.Net, the SQL statement is one continuous statement. Now its a doddle to test the SQL statement.

    Simon
    I like to see it, I am not new to SQL but I am by far an expert and often make a whole heap of mistakes, I guess I'll migrate to .net too since classic is ageing, thanks for the tip, eventually I'll get it and use the single string format.

  13. #13
    Join Date
    Feb 2010
    Posts
    21
    Quote Originally Posted by r937 View Post
    yes it is, you get everything except the paper
    figured it was but thought i'd check, kind of like the kindle that amazon have brought out, pretty cool stuff. Thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •