Left Outer Join/multi-part identifier error help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Left Outer Join/multi-part identifier error help

Author

Topic

iffi27
Starting Member

3 Posts

Posted - 2014-07-22 : 13:05:38

I'm writing a query where I have multiple left-outer joins but I keep getting multi-part identifier error. Not sure what I'm doing wrong. Can anyone help, see the query below?

SELECT gl.seg5 Natural
,gl.seg2 Office
,gl.seg3 Dept
,gl.seg4 Team
,gl.seg6 Sub
,gl.seg7 Tkpr
,gl.seg1 Comp
,'CHK' Source
,RTRIM(csddt.baid)+'-'+RTRIM(csddt.cknum)+'-'+CAST(csddt.ckline AS VARCHAR) Reference
,csd.cdbtid Batch
,csddt.currdate currdate --remove later
,csddt.ckdate Xdate
,CAST(ROUND(csddt.amt/cdrate,2) AS DECIMAL(14,2)) Amount
,csddt.currency Currency
,COALESCE(csddt.respparty,'') RespParty
,ap.apname Vendor
,csddes.des CHKDescription
,csddes.dsline Xline
,CAST(year(csddt.ckdate)as varchar)+'M'+RIGHT('0'+cast(month(csddt.ckdate)as varchar),2) as [Time]
,CAST(gl.seg1 as varchar)+'-'+CAST(gl.seg2 as varchar) PXOffice
,CAST(gl.seg3 as varchar)+'-'+CAST(gl.seg4 as varchar) PXDepartment
,CAST(gl.seg5 as varchar)+'-'+CAST(gl.seg6 as varchar) PXAccount
FROM gl, currates, csddt, apvo, csd
LEFT OUTER JOIN csddes ON csddt.baid = csddes.baid AND csddt.cknum = csddes.cknum AND csddt.ckline = csddes.ckline
AND csddes.gltype IN ('C','U')
AND csddes.dsline = 1
LEFT OUTER JOIN ap ON csd.apnum = ap.apnum
WHERE csddt.glnum = gl.glnum
AND csddt.gltype IN ('C','U')
AND csddt.ckdate BETWEEN dateadd(MOnth,-1,getdate()) AND dateadd(day,-1,getdate())--END DATE MUST BE COMPLETION OF LAST SUMMARY UPDATE/LOAD
AND csddt.baid = csd.baid
AND csddt.cknum = csd.cknum
AND csddt.currency = currates.curcode COLLATE DATABASE_DEFAULT
AND currates.trtype = 'D'
AND csddt.ckdate BETWEEN currates.cddate1 AND currates.cddate2
AND csddt.vo_id = apvo.vo_id

Errors

Msg 4104, Level 16, State 1, Line 68
The multi-part identifier "csddt.baid" could not be bound.
Msg 4104, Level 16, State 1, Line 68
The multi-part identifier "csddt.cknum" could not be bound.
Msg 4104, Level 16, State 1, Line 68
The multi-part identifier "csddt.ckline" could not be bound.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-07-22 : 13:22:53

Does the csddt table/view have those columns?

Also, it's considered poor form to NOT specify the join type you want (ANSI). Even if you really want a full outer joins.

iffi27
Starting Member

3 Posts

Posted - 2014-07-22 : 14:13:32

yes those tables are are in that csddt table.
What do you mean by defining join types? Wouldn't Left Outer Join be considered the join type? Sorry, I'm very new to SQL. It seems like, it's not identifying csddt table when it is declared in the FROM line.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-07-22 : 14:23:57

If you say those columns are in that table, then I'm not sure what to tell you.

As for the join type, look at a snippet of your code:

FROM gl, currates, csddt, apvo, csd
LEFT OUTER JOIN csddes

That is six tables with only one join specified. What you haven written is a side affect of being able to list tables and have sql cross join everything together. What you should be doing is similar to your LEFT JOINs. By using ANSI join syntax (INNER, LEFT, RIGHT, FULL, CROSS) to specify the join type. For example:

	...
FROM 
	gl 
INNER JOIN
	csddt
	ON csddt.glnum = gl.glnum
INNER JOIN
	csd		
	ON csddt.baid = csd.baid 
	AND csddt.cknum = csd.cknum 
	...

Cheers!

iffi27
Starting Member

3 Posts

Posted - 2014-07-22 : 16:23:23

Thanks Lamprey.
I cut down the query to pin point where I'm going wrong and am still getting this error with multi-part identifier 'csddt.baid' could not be found.

This might be more easier to understand if you can help...

select * from csddt, csd
LEFT OUTER JOIN csddes ON
csddt.baid = csddes.baid
LEFT OUTER JOIN ap ON
csd.apnum = ap.apnum

But if I write the same query this way, it produces results

select * from csddt
LEFT OUTER JOIN csddes ON
csddt.baid = csddes.baid, csd
LEFT OUTER JOIN ap ON
csd.apnum = ap.apnum

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-22 : 16:26:27

select *
from csddt
JOIN csd ON csddt.blah = csd.blah
LEFT JOIN csddes ON csddt.baid = csddes.baid
LEFT JOIN ap ON csd.apnum = ap.apnum

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources