The multi-part identifier could not be bound.

Question

Post reply

The multi-part identifier could not be bound.

kgillispie

SSC Enthusiast

Points: 173
More actions
August 13, 2012 at 12:44 pm

#280252

I've read some suggestions on this forum, but none were able to help.. so am posting a new question.
I am running the below query but I end up with this error
The multi-part identifier "P.PriceIndexId" could not be bound.
Any assistance would be greatly appreciated.
Select
A.PriceIndexId,
A.PriceTypeId,
substring(IsNull(A.Code,'') + '; ' + IsNull(A.Name, ''),1,40),
P.Price,
P.Differential,
A.CurrencyId,
A.UnitOfMeasureId,
A.ConversionMethodId,
E.[Percent],
B.CodeDescription,
E.AutoAverage,
E.LeaseBulk,
E.StreamBase,
E.PriceDerivedId,
E.Differential,
DER.CurrencyId,
P.Locked,
P.Price,
A.FacilityPricingId,
DER.FacilityPricingId
From Code B,
PriceDerived E,
PricePeriod P,
PriceIndex DER
LEFT JOIN PriceIndex A ON A.PriceIndexId = P.PriceIndexId
and ((P.Period = @Period) or (@Period is null))
Where ((E.PriceDerivedId = @PriceDerivedId) or (@PriceDerivedId is null))
andA.PriceIndexId = E.PriceIndexId
andE.PriceDerivedId = DER.PriceIndexId
andA.PriceTypeId = B.CodeId

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 1

Without seeing table definitions, nobody will be able to give you a definitive answer.

At a guess, should this line

LEFT JOIN PriceIndex A ON A.PriceIndexId = P.PriceIndexId

really be

LEFT JOIN PriceIndex A ON A.PriceIndexId = DER.PriceIndexId

Ant-Green SSC Guru Points: 113445 More actions · Answer 2

Is there deffinatly a column called PriceIndexID in PricePeriod?

If not which table is the column in as the join is wrong.

Also I would read this on catch all queries http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ to get around Where ((E.PriceDerivedId = @PriceDerivedId) or (@PriceDerivedId is null))

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

kgillispie (8/13/2012)
I've read some suggestions on this forum, but none were able to help.. so am posting a new question.
I am running the below query but I end up with this error
The multi-part identifier "P.PriceIndexId" could not be bound.
Any assistance would be greatly appreciated.
Select
A.PriceIndexId,
A.PriceTypeId,
substring(IsNull(A.Code,'') + '; ' + IsNull(A.Name, ''),1,40),
P.Price,
P.Differential,
A.CurrencyId,
A.UnitOfMeasureId,
A.ConversionMethodId,
E.[Percent],
B.CodeDescription,
E.AutoAverage,
E.LeaseBulk,
E.StreamBase,
E.PriceDerivedId,
E.Differential,
DER.CurrencyId,
P.Locked,
P.Price,
A.FacilityPricingId,
DER.FacilityPricingId
From Code B,
PriceDerived E,
PricePeriod P,
PriceIndex DER
LEFT JOIN PriceIndex A ON A.PriceIndexId = P.PriceIndexId
and ((P.Period = @Period) or (@Period is null))
Where ((E.PriceDerivedId = @PriceDerivedId) or (@PriceDerivedId is null))
andA.PriceIndexId = E.PriceIndexId
andE.PriceDerivedId = DER.PriceIndexId
andA.PriceTypeId = B.CodeId

You're missing three or four join predicates in your FROM list. Mixing old-style joins (WHERE A.PriceIndexId = E.PriceIndexId) with proper joins (LEFT JOIN PriceIndex A ON A.PriceIndexId = P.PriceIndexId) is a nonstarter. Try again with proper joins. Here's a start:

FROM Code B

INNER JOIN PriceDerived E -- ON...

(inner/left) JOIN PricePeriod P -- ON...

INNER JOIN PriceIndex DER

ON E.PriceDerivedId = DER.PriceIndexId

LEFT JOIN PriceIndex A ON A.PriceIndexId = P.PriceIndexId

and ((P.Period = @Period) or (@Period is null)) -- <<--- this makes no sense, why put it here?

and A.PriceIndexId = E.PriceIndexId

and A.PriceTypeId = B.CodeId

WHERE ((E.PriceDerivedId = @PriceDerivedId) or (@PriceDerivedId is null))

Edit: changed tags

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kgillispie SSC Enthusiast Points: 173 More actions · Answer 4

kgillispie

SSC Enthusiast

Points: 173

August 14, 2012 at 5:57 am

#1525442

Thanks for the help!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

kgillispie (8/14/2012)
Thanks for the help!

You're welcome, though I get the feeling that we could have given a lot more. Can you post your modified code? It's likely that folks can still make a few improvements - and it's always good to see the end result.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden