Left outer join, error 4104

Question

Left outer join, error 4104

bseeley

Old Hand

Points: 300
More actions
December 16, 2008 at 7:07 am

#128369

Below is a simplified version of a select statement that returns
the error
("Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "RRSINV20.PARTNUM" could not be bound.")
If I remove the last line it works fine?
("AND (RRSINV20.PARTNUM=RRSPRB1.REFERENCE)")
Also if table "RRSINV19" is not last in the list of tables, it also returns the same 4104 error?
Any help appreciated
SELECT
RRSPRB1.REFERENCE,RRSINV20.PARTNUM,RRSINV2.ITEMCOST,
RRSINV19.LOCATION,RRSINV1.INVCAT FROM
RRSINV2,RRSINV1,RRSINV20,RRSINV19
LEFT OUTER JOIN RRSPRB1 ON (RRSINV19.LOCATION=RRSPRB1.LOCATION)
AND (RRSINV20.PARTNUM=RRSPRB1.REFERENCE)

Viewing 9 posts - 1 through 8 (of 8 total)

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

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

The relationship between RRSINV20 and RRSINV19 is resolved in the WHERE clause, but you're referencing both in the FROM list in the joins to RRSPRB1, at which time this relationship is not yet known.

In your shoes, I'd change all of the referenced tables to appropriate JOINs.

^{“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

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

bseeley (12/16/2008)
Below is a simplified version of a select statement...
SELECT
RRSPRB1.REFERENCE,RRSINV20.PARTNUM,RRSINV2.ITEMCOST,
RRSINV19.LOCATION,RRSINV1.INVCAT FROM
RRSINV2,RRSINV1,RRSINV20,RRSINV19
LEFT OUTER JOIN RRSPRB1 ON (RRSINV19.LOCATION=RRSPRB1.LOCATION)
AND (RRSINV20.PARTNUM=RRSPRB1.REFERENCE)

Would it be possible to see the whole SELECT statement? There seem to be a number of things missing here, as you have 2 tables that look like they are not joined at all, and it looks like you are joining RRSINV20 improperly. Mixing old style comma delimited FROM clause and new style ANSI JOINS is also a bad idea. I'd suggest writing out explicit ANSI JOIN syntax for each table in your query, such as:

...

FROM RRSINV19 INNER JOIN RRSPRB1 ON (RRSINV19.LOCATION = RRSPRB1.LOCATION)

INNER JOIN RRSINV20 ON (RRSPRB1.REFERENCE = RRSINV20.PARTNUM)

...

bseeley Old Hand Points: 300 More actions · Answer 3

Thanks for the quick response

Below is the complete select statement

Also, I am converting a program that ran this under Foxpro (dbf's) to run under SQL Server 2005, the join did work on the old database

Again thanks

Barry

SELECT RRSINV19.REFERENCE,RRSINV19.LOCATION,RRSINV19.RECNO,RRSINV20.PARTNUM,

RRSINV20.DESCRIPT,RRSPRB1.LRETAIL,RRSINV2.ITEMCOST,RRSINV2.LISTPRICE,

RRSINV1.INVCAT,RRSINV2.SHELFLOC,RRSINV1.BARCODE,RRSINV1.PRIMVENREF,

RRSINV1.CONFACT,RRSINV1.PACKQTY,RRSINV1.TAXABLE,RRSINV20.POSTED,RRSINV20.RETPERUNIT,

RRSINV20.UNETCOST FROM

RRSINV2,RRSINV1,RRSINV19,RRSINV20

LEFT OUTER JOIN RRSPRB1 ON RRSPRB1.REFERENCE = RRSINV20.PARTNUM

AND

RRSPRB1.LOCATION = RRSINV19.LOCATION

WHERE RRSINV19.REFERENCE = '3929101' AND

RRSINV19.VENREF = 'AMER' AND RRSINV20.REFLINK = (RRSINV19.RECNO) AND

RRSINV2.REFERENCE = RRSINV20.PARTNUM AND RRSINV2.LOCATION = RRSINV19.LOCATION AND

RRSINV1.REFERENCE = RRSINV20.PARTNUM AND ((RRSINV20.POSTED = 'T' AND

CONVERT(REAL,COALESCE(RRSPRB1.LRETAIL,'0')) <> CONVERT(REAL,COALESCE(RRSINV2.LISTPRICE,'0'))) OR

(RRSINV20.POSTED = 'F' AND

CONVERT(REAL,COALESCE(RRSPRB1.LRETAIL,'0')) <> CONVERT(REAL,COALESCE(RRSINV20.RETPERUNIT,'0'))))

ORDER BY RRSINV1.INVCAT,RRSINV20.PARTNUM

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

SELECT RRSINV19.REFERENCE, RRSINV19.LOCATION, RRSINV19.RECNO, RRSINV20.PARTNUM,

RRSINV20.DESCRIPT, RRSPRB1.LRETAIL, RRSINV2.ITEMCOST, RRSINV2.LISTPRICE,

RRSINV1.INVCAT, RRSINV2.SHELFLOC, RRSINV1.BARCODE, RRSINV1.PRIMVENREF,

RRSINV1.CONFACT, RRSINV1.PACKQTY, RRSINV1.TAXABLE, RRSINV20.POSTED, RRSINV20.RETPERUNIT,

RRSINV20.UNETCOST

FROM RRSINV2

INNER JOIN RRSINV19 ON RRSINV19.LOCATION = RRSINV2.LOCATION

INNER JOIN RRSINV20 on RRSINV20.REFLINK = RRSINV19.RECNO AND RRSINV20.PARTNUM = RRSINV2.REFERENCE

INNER JOIN RRSINV1 ON RRSINV1.REFERENCE = RRSINV20.PARTNUM

LEFT JOIN RRSPRB1 ON RRSPRB1.REFERENCE = RRSINV20.PARTNUM AND RRSPRB1.LOCATION = RRSINV19.LOCATION

WHERE RRSINV19.REFERENCE = '3929101'

AND RRSINV19.VENREF = 'AMER'

AND

((RRSINV20.POSTED = 'T' AND

CONVERT(REAL,COALESCE(RRSPRB1.LRETAIL,'0')) <> CONVERT(REAL,COALESCE(RRSINV2.LISTPRICE,'0'))) OR

(RRSINV20.POSTED = 'F' AND

CONVERT(REAL,COALESCE(RRSPRB1.LRETAIL,'0')) <> CONVERT(REAL,COALESCE(RRSINV20.RETPERUNIT,'0'))))

ORDER BY RRSINV1.INVCAT, RRSINV20.PARTNUM

^{“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

Garadin One Orange Chip Points: 29613 More actions · Answer 5

Try this one?

[font="Courier New"]SELECT -- 1

RRSPRB1.LRETAIL,

RRSINV1.INVCAT,

RRSINV1.BARCODE,

RRSINV1.PRIMVENREF,

RRSINV1.CONFACT,

RRSINV1.PACKQTY,

RRSINV1.TAXABLE,

-- 2

RRSINV2.SHELFLOC,

RRSINV2.ITEMCOST,

RRSINV2.LISTPRICE,

-- 19

RRSINV19.REFERENCE,

RRSINV19.LOCATION,

RRSINV19.RECNO,

-- 20

RRSINV20.POSTED,

RRSINV20.RETPERUNIT,

RRSINV20.UNETCOST,

RRSINV20.PARTNUM,

RRSINV20.DESCRIPT,

FROM

RRSINV2

INNER JOIN RRSINV19 ON RRSINV2.LOCATION = RRSINV19.LOCATION

INNER JOIN RRSINV20 ON RRSINV2.REFERENCE = RRSINV20.PARTNUM AND RRSINV20.REFLINK = RRSINV19.RECNO

INNER JOIN RRSINV1 ON RRSINV1.REFERENCE = RRSINV20.PARTNUM

LEFT OUTER JOIN RRSPRB1 ON RRSPRB1.REFERENCE = RRSINV20.PARTNUM

WHERE RRSINV19.REFERENCE = '3929101' AND RRSINV19.VENREF = 'AMER' AND

((RRSINV20.POSTED = 'T' AND RRSPRB1.LOCATION = RRSINV19.LOCATION AND -- Note, this affects your left join.

CONVERT(REAL,COALESCE(RRSPRB1.LRETAIL,'0')) <> CONVERT(REAL,COALESCE(RRSINV2.LISTPRICE,'0')))

OR

(RRSINV20.POSTED = 'F' AND

CONVERT(REAL,COALESCE(RRSPRB1.LRETAIL,'0')) <> CONVERT(REAL,COALESCE(RRSINV20.RETPERUNIT,'0'))))

ORDER BY

RRSINV1.INVCAT,RRSINV20.PARTNUM[/font]

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

bseeley Old Hand Points: 300 More actions · Answer 6

Thanks, seems to work fine

Appreciate all the replies, I need to study joins a little more

Barry

bseeley Old Hand Points: 300 More actions · Answer 7

Thanks, seems to work fine

Appreciate all the replies, I need to study joins a little more

Barry

J-440512 SSCertifiable Points: 6285 More actions · Answer 8

I also suggest using aliases for your table names. If all the names begin with "RRSIN", then you can make the code less verbose -- I would have to verify each table name to make sure that there is not one called "RSSIN" - can't help myself.

[font="Courier New"]SELECT -- 1

B1.LRETAIL,

V1.INVCAT,

V1.BARCODE,

V1.PRIMVENREF,

V1.CONFACT,

V1.PACKQTY,

V1.TAXABLE,

-- 2

V2.SHELFLOC,

V2.ITEMCOST,

V2.LISTPRICE,

-- 19

V19.REFERENCE,

V19.LOCATION,

V19.RECNO,

-- 20

V20.POSTED,

V20.RETPERUNIT,

V20.UNETCOST,

V20.PARTNUM,

V20.DESCRIPT,

FROM

RRSINV2 V2

INNER JOIN RRSINV19 V19 ON V2.LOCATION = V19.LOCATION

INNER JOIN RRSINV20 V20 ON V20.REFERENCE = V2.PARTNUM AND V20.REFLINK = V19.RECNO

INNER JOIN RRSINV1 V1 ON V1.REFERENCE = V20.PARTNUM

LEFT OUTER JOIN RRSPRB1 B1 ON B1.REFERENCE = V20.PARTNUM[/font]