January 22, 2010 at 5:38 am
When I run this select query I get the message:
The multi-part identifier "cicmpy.debnr" could not be bound.
Can anybody tell me what I must change?
select cicmpy.debnr
FROM dbo.orsrg WITH (nolock) LEFT OUTER JOIN
dbo.ItemNumbers WITH (nolock) INNER JOIN
dbo.cicmpy WITH (nolock) INNER JOIN
dbo.orkrg WITH (nolock) ON cicmpy.debnr = dbo.orkrg.debnr ON dbo.ItemNumbers.Associate = m.cmp_wwn LEFT OUTER JOIN
dbo.Items Items_2 WITH (nolock) ON dbo.ItemNumbers.ItemCode = Items_2.ItemCode ON dbo.orsrg.serie_num = dbo.ItemNumbers.Number AND
dbo.orsrg.ordernr = dbo.orkrg.ordernr LEFT OUTER JOIN
dbo.Items Items_1 WITH (nolock) RIGHT OUTER JOIN
dbo.recipe WITH (nolock) ON Items_1.ItemCode = dbo.recipe.itemreq RIGHT OUTER JOIN
dbo.Items WITH (nolock) ON dbo.recipe.itemprod = dbo.Items.ItemCode ON dbo.orsrg.artcode = dbo.Items.ItemCode
WHERE (dbo.orkrg.afgehandld = 0)
AND (dbo.orsrg.PakbonNr IS NULL)
AND (Items.useryesno_02 = 1)
AND (ItemNumbers.associate is not null)
and (recipe.itemreq is not null)
and (recipe.id = '918' )
AND (Items_1.Type = 'L')
and (orsrg.id ='230262')
Already thanks,
Henk-Jan
January 22, 2010 at 6:13 am
Does column debnr exist in cicmpy table (or view)?
January 22, 2010 at 6:40 am
This was removed by the editor as SPAM
January 22, 2010 at 7:07 am
If I were you mate, I'd ditch this and start all over again.
Looking at the two right-joined tables recipe and items, seems they would be best listed first as the FROM table and the first join. Then take it from there. Can you give a good verbal explanation of what you are trying to do?
BTW there's an undeclared alias used in the query, 'm':
select cicmpy.debnr
FROM dbo.orsrg WITH (nolock)
LEFT OUTER JOIN dbo.ItemNumbers WITH (nolock)
INNER JOIN dbo.cicmpy WITH (nolock)
INNER JOIN dbo.orkrg WITH (nolock)
LEFT OUTER JOIN dbo.Items Items_2 WITH (nolock)
LEFT OUTER JOIN dbo.Items Items_1 WITH (nolock)
RIGHT OUTER JOIN dbo.recipe WITH (nolock)
RIGHT OUTER JOIN dbo.Items WITH (nolock)
ON cicmpy.debnr = dbo.orkrg.debnr
ON dbo.ItemNumbers.Associate = m.cmp_wwn -- <-- no alias 'm' declared
ON dbo.ItemNumbers.ItemCode = Items_2.ItemCode
ON dbo.orsrg.serie_num = dbo.ItemNumbers.Number AND dbo.orsrg.ordernr = dbo.orkrg.ordernr
ON Items_1.ItemCode = dbo.recipe.itemreq
ON dbo.recipe.itemprod = dbo.Items.ItemCode
ON dbo.orsrg.artcode = dbo.Items.ItemCode
WHERE (dbo.orkrg.afgehandld = 0)
AND (dbo.orsrg.PakbonNr IS NULL)
AND (Items.useryesno_02 = 1)
AND (ItemNumbers.associate is not null)
and (recipe.itemreq is not null)
and (recipe.id = '918' )
AND (Items_1.Type = 'L')
and (orsrg.id = '230262')
Sometimes reorganising the query into a standardised format is all it takes to figure out a problem. There appear to be two main standard formats, choose one and stick to it.
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
January 22, 2010 at 7:22 am
I love this forum, since I now uses it. You are helping me solving some isues.
Chris, your note I will look in a few days
January 22, 2010 at 7:29 am
This was removed by the editor as SPAM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply