suffa suffa - 3 months ago 12
SQL Question

SQL Server 2014 sql mulitpart identifier cannot be bound

I'm still a neophyte when it comes to SQL Server. So, can somewhat direct me in why I'm getting the following error:


Msg 4104, Level 16, State 1, Line 16

The multi-part identifier "CSetOrder.OrderItemID" could not be bound.


This is the script:

SELECT
OrderItem.OrderID
, OrderItem.OrderItemID
, OrderItem.DID
, CSet.NNumber
, OrderItem.Amount
, OrderItem.Quantity
, OrderItem.Quantity
, CSetOrder.CSetOrderID
, CSet.CSetID
, CSet.Required
FROM
CSet
INNER JOIN
OrderItem ON OrderItem.OrderItemID = ClaimSetOrder.OrderItemID
INNER JOIN
CSetOrder ON CSetOrder.CSetID = CSet.CSetID
WHERE
OrderItem.OID = XXXX

Answer

You need to specify the table before using it in the on clause. So, rearrange the order of the conditions in the FROM:

FROM ClaimSet INNER JOIN
     ClaimSetOrder
     ON ClaimSetOrder.ClaimSetID = ClaimSet.ClaimSetID INNER JOIN
     OrderLineItem
     ON OrderLineItem.OrderLineItemID = ClaimSetOrder.OrderLineItemID 

Note: I would also recommend that you use table aliases to simplify the query construction:

FROM ClaimSet cs INNER JOIN
     ClaimSetOrder cso
     ON cso.ClaimSetID = cs.ClaimSetID INNER JOIN
     OrderLineItem oli
     ON oli.OrderLineItemID = cso.OrderLineItemID 

This requires changing all the column references throughout the query.

Comments