February 9, 2009 at 8:57 am
Newbie sql user. Created query below and ran in MS SQL Studio Manager. Keep getting the following error:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v.vend_num" could not be bound.
Think the problem is relating to the left outer joins. Tried switching the order of the 2 joins but then I get same error for both v.vend_num and s.slsman.
I have checked the aliases and field names several times thinking there was a typo. Even dragged and dropped table/field names from object window and still get same error. I know it probably something simple but I can't seem to find it.
Please help! Thanks.
Ron
Query:
select c.cust_num as customercustnum, c.cust_seq, c.contact##1, c.phone##1, t.description as termsdesc, c.tax_code1 as custtaxcode1,
c.slsman as custsalesman, ca.name, ca.addr##1, ca.addr##2, ca.addr##3, ca.addr##4, ca.city, ca.state, ca.country, ca.zip,
o.co_num, o.cust_num as cocustnum, o.cust_seq, o.order_date as coorddate, o.contact as cocontact, o.phone as cophone, o.price as coprice,
o.slsman as cosaleman, o.tax_code1 as cotaxcode1, tx.description, i.cust_num as coitemcustnum, i.co_line, i.co_release,
i.item, it.description, i.ship_date,
s.slsman as slsmanslsman, s.ref_num as slsmanrefnum,
v.vend_num, v.name
from customer c, custaddr ca, terms t, coitem i, item it, vendaddr v, taxcode tx WITH (NOLOCK)
left outer join slsman as s WITH (NOLOCK) ON s.ref_num = v.vend_num
left outer join co as o WITH (NOLOCK) ON s.slsman = o.slsman
where c.cust_num = ca.cust_num
and c.cust_seq = ca.cust_seq
and c.cust_seq = 0
and c.terms_code = t.terms_code
and c.cust_num = o.cust_num
and c.cust_seq = o.cust_seq
and o.co_num = i.co_num
and i.co_line = 1
and o.cust_seq = 0
and i.item = it.item
and o.tax_code1 = tx.tax_code
February 9, 2009 at 9:09 am
Are you sure the column is vend_num?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 9, 2009 at 9:18 am
This is because you are mixing using the old-standard non-ansi method of joining, i.e. relations are added to WHERE clause instead of ON clause. So, in this query, the table slsman can only access the subsequent table defined before it (i.e. taxcode).
And also you don't have any linking between tables vendaddr & taxcode
Here modified the query to the conventional ansi standard...
SELECTc.cust_num AS customercustnum, c.cust_seq, c.contact##1, c.phone##1,
t.description AS termsdesc, c.tax_code1 AS custtaxcode1, c.slsman AS custsalesman,
ca.name, ca.addr##1, ca.addr##2, ca.addr##3, ca.addr##4, ca.city, ca.state, ca.country,
ca.zip, o.co_num, o.cust_num AS cocustnum, o.cust_seq, o.order_date AS coorddate,
o.contact AS cocontact, o.phone AS cophone, o.price AS coprice, o.slsman AS cosaleman,
o.tax_code1 AS cotaxcode1, tx.description, i.cust_num AS coitemcustnum, i.co_line,
i.co_release, i.item, it.description, i.ship_date, s.slsman AS slsmanslsman,
s.ref_num AS slsmanrefnum, v.vend_num, v.name
FROMcustomer c
INNER JOIN custaddr ca ON c.cust_num = ca.cust_num and c.cust_seq = ca.cust_seq
INNER JOIN terms t ON c.terms_code = t.terms_code
INNER JOIN coitem i ON i.co_line = 1
INNER JOIN item it ON i.item = it.item
INNER JOIN vendaddr v ON 1 = 1 --here join criteria is missing, verify the query
INNER JOIN taxcode tx ON 1 = 1 --here join criteria is missing, verify the query
LEFT OUTER JOIN slsman s ON v.vend_num = s.ref_num
LEFT OUTER JOIN co o ON s.slsman = o.slsman
WHEREc.cust_seq = 0
AND c.cust_num = o.cust_num --this would make the above "co" LEFT JOIN to INNER JOIN
AND c.cust_seq = o.cust_seq--this would make the above "co" LEFT JOIN to INNER JOIN
AND o.co_num = i.co_num --this would make the above "co" LEFT JOIN to INNER JOIN
AND o.cust_seq = 0
AND o.tax_code1 = tx.tax_code --this would make the above "co" LEFT JOIN to INNER JOIN
--Ramesh
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply