Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2006
    Posts
    4

    Unanswered: SQL HELP! Msg 4104, Level 16, State 1, Line 1 - The multi-part identifier error

    Hi chaps,

    I have the following SQL query (SQL 2005).
    Its basically retrieving some values using simple joins.
    However there appears to be a problem with the LEFT OUTER JOIN:
    "LEFT OUTER JOIN DDDispatchedOrder ON (OrderLineItemTransaction.OrderLineItemTransaction ID = DDDispatchedOrder.OrderItemTransactionID)
    "
    When I try to compile the code i Get :
    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "OrderLineItemTransaction.OrderLineItemTransaction ID" could not be bound.

    Any help would be appreciated.

    Cheers
    Bal

    SELECT
    ord.orderDate,
    cc.forename + ' ' + cc.surname person,
    prod.description,
    oli.noofitems,
    deladdr.housenameno + ' ' + deladdr.addressLine1 + ' ' + deladdr.addressLine2 + ' ' + deladdr.city + ' ' + deladdr.postcode + ' ' + deladdr.county + ' ' + deladdr.country deladdress
    FROM
    product prod,
    OrderLineItem oli,
    [Order] ord,
    OrderTransaction ordT,
    OrderLineItemTransaction oliT,
    CustomerContact cc,
    Customer cust,
    DDDispatchedOrder dd,
    address deladdr,
    address invaddr
    LEFT OUTER JOIN DDDispatchedOrder ON (OrderLineItemTransaction.OrderLineItemTransaction ID = DDDispatchedOrder.OrderItemTransactionID)
    WHERE
    prod.productID = oli.productID
    AND ord.orderID = oli.orderID
    AND ord.orderID = ordT.orderID
    AND oliT.orderlineitemID = oli.orderlineitemID
    AND cc.customercontactID = ord.customercontactID
    AND cc.customerID = cust.customerID
    AND ord.invoiceaddressID = invaddr.addressID
    AND ord.deliveryaddressID = deladdr.addressID
    AND ordT.dispatchTypeID = 2

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Try putting OrderLineItemTransaction ID in square brackets. The query optimizer may be choking on the space there. See this:

    http://msdn.microsoft.com/library/de...on_03_89rn.asp

  3. #3
    Join Date
    Sep 2006
    Posts
    4
    Thanks for the reply.

    Alas, it did not work, still getting the same error message.

    Thanks though.

    Bal

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the space is definitely the cause of the error

    try removing it

    also, since you gave the OrderLineItemTransaction table the alias name oliT, you may have to use the alias in the ON clause --

    LEFT OUTER JOIN DDDispatchedOrder
    ON (oliT.OrderLineItemTransactionID = DDDispatchedOrder.OrderItemTransactionID)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2006
    Posts
    4
    Thank you for the advice, still the same error I'm afraid.

    Cheers
    Bal:

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, i'm afraid you're just going to have to look up each table and find out what the columns are actually called
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2006
    Posts
    4

    Cracked it

    It now works.

    I changed the order of the tables to:

    FROM product prod,
    Supplier supp,
    OrderLineItem oli,
    [Order] ord,
    OrderTransaction ordT,
    CustomerContact cc,
    Customer cust,
    DDDispatchedOrder dd,
    address deladdr,
    address invaddr ,
    OrderLineItemTransaction oliT
    LEFT OUTER JOIN DDDispatchedOrder ON (oliT.OrderLineItemTransactionID = DDDispatchedOrder.OrderItemTransactionID)

    * Note the moving of "OrderLineItemTransaction oliT" to the line before the outer join.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code like a pro. Define relationships between the tables with JOINs, not in the WHERE clause.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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