CODEWITHSUNDEEP
sqlsql-serverssmswhere-clauseidentifier
Ana
Ana

Reputation: 35

The multi-part identifier could not be bound sql where clause

I am trying to multiply two tables using the code below to get a new calculated column. I am using the SQL Server Express Edition and SQL Server Management Studio.

select 
    [Yield_unpiv].[SubPrecinct],
    [Yield_unpiv].[LandUse],
    [Yield_unpiv].[Yield] * [LU_Rules_Final].[AM_GenRateFinal] * [LU_Rules_Final].[AM_In_factor] / [LU_Rules_Final].[UnitValue] as AM_In
from 
    Yield_unpiv, LU_Rules_Final
where 
    [Yield_unpiv].[LandUse]=[LU_Rules_Final].[LandUse]

However, I get the following error

The multi-part identifier "Yield_unpiv.LandUse" could not be bound.

The Yield_unpiv is set up like this:

SubPrecinct | Yield     | LandUse
P1            Unique #s   LDResi 
P1                        MDResi 
.                           .
.                           .
.                           .
P2                       LDResi 
P2                       MDResi 
.                           .
.                           .
.                           .

And the LU_Rules_Final is set up like this:

LandUse    | UnitValue | AM_GenRateFinal | AM_In_factor
LDResi       1             2.5                0.5
BulkyGoods   100           7                  0.7
MDREsi       1             0.52               0.1  
.
.
.

Not sure why I am getting this multi-part identifier "Yield_unpiv.LandUse" could not be bound error.

Please help!

Upvotes: 0

Views: 954

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521259

I don't know the exact cause of your error, but it likely has to do with that the column LandUse appears in both tables. I speculate that refactoring it to use explicit joins would fix the problem:

SELECT
    t1.[SubPrecinct],
    t1.[LandUse],
    t1.[Yield] * t2.[AM_GenRateFinal] * t2.[AM_In_factor] / t2.[UnitValue] AS AM_In
FROM Yield_unpiv t1
INNER JOIN LU_Rules_Final t2
    OM t1.[LandUse] = t2.[LandUse];

If this fixes your problem, then it means one more reason not to use the archaic pre ANSI-92 style of SQL joins. Always use explicit joins whenever possible.

Upvotes: 1

Related Questions