May 17, 2012 at 9:46 am
Hi,
I'm having some trouble with what I thought was a fairly easy query...
select CMPCOMPANYCODE
,CMPCOMPANYNAME
,CMPCOID
,GLRBASEACCT
,GLRGLCODETYPE
,GLRCOMPONENTCODE
,GLRGLSEGSEQCODE
,GLQSEGSEQDESC
,GLQSEGMENTSEQUENCE
,GLQSEGMENT0
,GLQCONSTANTSEGMENTVALUE0
,GLQSEGMENT1
,GLQCONSTANTSEGMENTVALUE1
,GLQSEGMENT2
,GLQCONSTANTSEGMENTVALUE2
,GLQSEGMENT3
,GLQCONSTANTSEGMENTVALUE3
,GLQSEGMENT4
,GLQCONSTANTSEGMENTVALUE4
,GLQSEGMENT5
,GLQCONSTANTSEGMENTVALUE5
,GLQSEGMENT6
,GLQCONSTANTSEGMENTVALUE6
,GLQSEGMENT7
,GLQCONSTANTSEGMENTVALUE7
,eaccoid
,eacaccrcode
,EacAccrOption
from GLDEFRLS
INNER JOIN accropts AC
ON AC.AccAccrOption = EM.EacAccrOption
INNER JOIN empaccr EM
ON EM.EacAccrOption = AccAccrOption
INNER JOIN GLSEGSEQ
ON GLRGLSEGSEQCODE = GLQGLSEGSEQCODE and
--GLQGLSEGSEQCODE = 'seg08' --and
glrglcodeTYPE LIKE 'A%'
INNER JOIN company
ON GLRCOID = CMPCOID
where dbo.wsi_f_get_companycode(glrcoid) in ('FSL','FLT','SMS','SSC','TSI','CHW','ESL','SSL')
-- AND GLRBASEACCT = '5208'
--INNER JOIN accropts
-- ON AccAccrOption = EacAccrOption
ORDER BY CMPCOMPANYCODE
,GLRCOMPONENTCODE
,GLRGLCODETYPE
It says "The multi-part identifier "EM.EacAccrOption" could not be bound.'
can't seem to figure it out..
May 17, 2012 at 9:50 am
krypto69 (5/17/2012)
Hi,I'm having some trouble with what I thought was a fairly easy query...
select CMPCOMPANYCODE,CMPCOMPANYNAME
,CMPCOID
,GLRBASEACCT
,GLRGLCODETYPE
,GLRCOMPONENTCODE
,GLRGLSEGSEQCODE
,GLQSEGSEQDESC
,GLQSEGMENTSEQUENCE
,GLQSEGMENT0
,GLQCONSTANTSEGMENTVALUE0
,GLQSEGMENT1
,GLQCONSTANTSEGMENTVALUE1
,GLQSEGMENT2
,GLQCONSTANTSEGMENTVALUE2
,GLQSEGMENT3
,GLQCONSTANTSEGMENTVALUE3
,GLQSEGMENT4
,GLQCONSTANTSEGMENTVALUE4
,GLQSEGMENT5
,GLQCONSTANTSEGMENTVALUE5
,GLQSEGMENT6
,GLQCONSTANTSEGMENTVALUE6
,GLQSEGMENT7
,GLQCONSTANTSEGMENTVALUE7
,eaccoid
,eacaccrcode
,EacAccrOption
from GLDEFRLS
INNER JOIN accropts AC
ON AC.AccAccrOption = EM.EacAccrOption
INNER JOIN empaccr EM
ON EM.EacAccrOption = AccAccrOption
INNER JOIN GLSEGSEQ
ON GLRGLSEGSEQCODE = GLQGLSEGSEQCODE and
--GLQGLSEGSEQCODE = 'seg08' --and
glrglcodeTYPE LIKE 'A%'
INNER JOIN company
ON GLRCOID = CMPCOID
where dbo.wsi_f_get_companycode(glrcoid) in ('FSL','FLT','SMS','SSC','TSI','CHW','ESL','SSL')
-- AND GLRBASEACCT = '5208'
--INNER JOIN accropts
-- ON AccAccrOption = EacAccrOption
ORDER BY CMPCOMPANYCODE
,GLRCOMPONENTCODE
,GLRGLCODETYPE
It says "The multi-part identifier "EM.EacAccrOption" could not be bound.'
can't seem to figure it out..
Are you sure this join criteria is correct here:
...
from GLDEFRLS
INNER JOIN accropts AC
ON AC.AccAccrOption = EM.EacAccrOption
May 17, 2012 at 9:53 am
Hi
You are trying to JOIN on a table that you havent defined yet:
from GLDEFRLS
INNER JOIN accropts AC
ON AC.AccAccrOption = EM.EacAccrOption
You are joining AC.AccAccrOption on EM.EacAccrOption but this table has not been introduced yet in your FROM
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 17, 2012 at 9:59 am
The problem is here:
from GLDEFRLS
INNER JOIN accropts AC
ON AC.AccAccrOption = EM.EacAccrOption
INNER JOIN empaccr EM
ON EM.EacAccrOption = AccAccrOption
You're referencing the alias EM before you've defined the alias. JOINS are processed in order, so the first join is
from GLDEFRLS
INNER JOIN accropts AC
ON AC.AccAccrOption = EM.EacAccrOption
and it cannot be evaluated because there is no EM defined at that point.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 17, 2012 at 10:42 am
Oh okay...but then how could I reference it then?
May 17, 2012 at 10:44 am
krypto69 (5/17/2012)
Oh okay...but then how could I reference it then?
Look with eye:
GLDEFRLS
INNER JOIN accropts AC
ON AC.AccAccrOption = EM.EacAccrOption
You are joining the table GLDEFRLS (with no alias) to the table accropts (with the alias AC) on the columns AC.AccAccrOption = EM.EacAccrOption
May 17, 2012 at 11:12 am
You have a couple of options depending on what you want. As Lynn is trying to point out you have an implicit CROSS JOIN, so you can make it explicit.
from GLDEFRLS
CROSS JOIN accropts AC
INNER JOIN empaccr EM
ON EM.EacAccrOption = AccAccrOption
The other option is to just change the order of the joins. (That won't resolve the implicit CROSS JOIN on GLDEFRLS.)
from accropts AC
INNER JOIN empaccr EM
ON EM.EacAccrOption = AC.AccAccrOption
INNER JOIN GLDEFRLS
ON EM.EacAccrOption = AC.AccAccrOption -- always true given previous join
Finally, you could change the first join conditions to remove the implicit CROSS JOIN. Since we don't know the fields in GLDEFRLS, we can't tell you which field should be used for the JOIN condition.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply