June 17, 2010 at 3:41 pm
This CTE query works fine without the linked server fields and the join. The select (commented out below) from the linked server works fine also, separately. When I join them with or without aliases, I get this error on all 4 columns:
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "SMSSMNMIRPT011.CM_007._sde.v_HR_NSV.EMPLID" could not be bound.
;WITH CTE AS (
SELECT
lastname
,firstname
,authstatus
,CASE WHEN ISNUMERIC(RIGHT(emplid,len(emplid)-1))=1 THEN dbo.PaddedEmpID(RIGHT(EMPLID,LEN(EMPLID)-1)) ELSE RIGHT(EMPLID,LEN(EMPLID)-1) END EmpID
,SMSSMNMIRPT011.CM_007._sde.v_HR_NSV.EMPLID
,SMSSMNMIRPT011.CM_007._sde.v_HR_NSV.LAST_NAME
,SMSSMNMIRPT011.CM_007._sde.v_HR_NSV.FIRST_NAME
,SMSSMNMIRPT011.CM_007._sde.v_HR_NSV.EMAIL_ID
FROM XLTEST_SP2...Sheet1$
)
SELECT lastname
,firstname
,empid
FROM CTE
LEFT JOIN SMSSMNMIRPT011.CM_007._sde.v_HR_NSV
ON CTE.EmpID = SMSSMNMIRPT011.CM_007._sde.v_HR_NSV.Emplid
--select top 1000 * from SMSSMNMIRPT011.CM_007._sde.v_HR_NSV
Thanks for any help with this.
Duane
June 17, 2010 at 4:08 pm
The problem appears to be that you are trying to put the linked server fields inside the CTE. Try the following:
;WITH CTE AS (
SELECT
lastname
,firstname
,authstatus
,CASE WHEN ISNUMERIC(RIGHT(emplid,len(emplid)-1))=1 THEN dbo.PaddedEmpID(RIGHT(EMPLID,LEN(EMPLID)-1)) ELSE RIGHT(EMPLID,LEN(EMPLID)-1) END EmpID
FROM XLTEST_SP2...Sheet1$
)
SELECT lastname
,c1.firstname
,c1.email
,c1.empid
,cm7.SMSSMNMIRPT011.CM_007._sde.v_HR_NSV.EMPLID
,cm7.SMSSMNMIRPT011.CM_007._sde.v_HR_NSV.LAST_NAME
,cm7.SMSSMNMIRPT011.CM_007._sde.v_HR_NSV.FIRST_NAME
,cm7.SMSSMNMIRPT011.CM_007._sde.v_HR_NSV.EMAIL_ID
FROM CTE c1
LEFT JOIN SMSSMNMIRPT011.CM_007._sde.v_HR_NSV cm7
ON c1..EmpID = cm7..Emplid
June 18, 2010 at 7:02 am
Thank you for getting me thinking in the right way. Your answer didn't work. It didn't like the 5 part columns in the CTE or the double dots .. in the JOIN. BUT, after playing around a few minutes, I came up with this and it DID work:
;WITH CTE AS (
SELECT
lastname
,firstname
,authstatus
,CASE WHEN ISNUMERIC(RIGHT(emplid,len(emplid)-1))=1 THEN dbo.PaddedEmpID(RIGHT(EMPLID,LEN(EMPLID)-1)) ELSE RIGHT(EMPLID,LEN(EMPLID)-1) END EmpID
FROM XLTEST_SP2...Sheet1$
)
SELECT lastname
,c1.firstname
,c1.email
,c1.empid
,cm7.EMPLID
,cm7.LAST_NAME
,cm7.FIRST_NAME
,cm7.EMAIL_ID
FROM CTE c1
LEFT JOIN SMSSMNMIRPT011.CM_007._sde.v_HR_NSV cm7
ON c1.EmpID = cm7.Emplid
June 18, 2010 at 7:31 am
It didn't like the 5-part columns in the cte because the table you joined wasn't part of the CTE. It may help conceptually to think of the CTE as a view. A view can only know about the objects that are included in it at the time it is created. Likewise, within a CTE, you can't reference any objects from the query that follows the CTE.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 18, 2010 at 10:33 am
Thank you. I have just started using CTEs in the last few weeks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply