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.
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_idErrorsMsg 4104, Level 16, State 1, Line 68The multi-part identifier "csddt.baid" could not be bound.Msg 4104, Level 16, State 1, Line 68The multi-part identifier "csddt.cknum" could not be bound.Msg 4104, Level 16, State 1, Line 68The 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, csdLEFT 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.glnumINNER 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 ONcsddt.baid = csddes.baid LEFT OUTER JOIN ap ON csd.apnum = ap.apnum But if I write the same query this way, it produces resultsselect * from csddtLEFT OUTER JOIN csddes ONcsddt.baid = csddes.baid, csdLEFT OUTER JOIN ap ON csd.apnum = ap.apnum |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-22 : 16:26:27
|
select * from csddtJOIN csd ON csddt.blah = csd.blahLEFT JOIN csddes ON csddt.baid = csddes.baid LEFT JOIN ap ON csd.apnum = ap.apnumTara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|