How could this happen? - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

How could this happen?

Author

Topic

sahu74
Posting Yak Master

100 Posts

Posted - 2005-03-31 : 09:56:49

I have the following query which gives me an error if I remove the commented lines. If I comment out the lines (which I have here), the query runs fine. What puzzles me is how could this happen when the lines next to the commented lines have the same structure but runs fine. Also, I would like to mention that I am running this query on SERVER1 on which SERVER2 is a linked server.

Any help will be appreciated.

Thank you.
PKS.


Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'T1' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'T1' does not match with a table name or alias name used in the query.


SELECT 	T1.EID,T1.ENAME,T1.EFF_DATE,T1.ETCODE, T1.ESTAT,T1.SPCLSTAT,T1.ORIGHRDT,T1.EMTDT,
       	T1.EJCLSS,T1.HDEPT,T1.BENEFIT_ELIGIBLE, T1.PARTFULL,T1.EFTE,T1.ANNBASRT,T1.CURBSPRD,
       	T3.SEX, T3.BIRTHDT, T3.ETH, T3.EDUCLVL, T5.CTZNVISA, T2.JCLASS,T2.P_NBR,T2.ACC_NBR,T2.XOBJECT,
       	T2.BUD_PCT_EFF,T2.BUD_FTE,T2.APPT_BEG_DATE, T2.APPT_END_DATE,T2.FTB,T2.ENCUMBERED_AMT,  
       	T2.MONTHLY_RATE,T2.SUPPLEMENT_TYPE, T4.DEPTNO1,T4.DEPTNO2,T4.DEPTNO3,T4.PROFRANK,T4.TNRESTAT, 
       	T4.TNRESTDT,T4.FCLTYTP,T4.VPIND,T4.VPASSOCI, T4.VPASSTI,T4.DEANIND,T4.DEANASSC,T4.DEANASST,T4.CHAIRIND, 
       	T4.ENDOWIND,T4.HORNIND,T4.RSRCHIND,T4.ADJNCTI,T4.VSTNGIND, T4.EMRTSIND,T4.OTHERIND,T4.DISTPROF,T4.PRMFULDT,
       	T4.PRMASSCD,T4.PRMASSTD,T4.PRMOTHRD  
FROM 	SERVER2.TECH.DBO.TRBASCTB T1, SERVER2.BUD.DBO.BDAPTCTB T2, SERVER2.TECH.DBO.TRBIOCTB T3,
     	SERVER2.TECH.DBO.TRFACCTB T4, SERVER2.TECH.DBO.TRJOBCTB J1, SERVER2.TECH.DBO.TRI9DCTB T5
WHERE 	T1.ORGID = 'TT'
AND T1.ETCODE = 'T'
AND T2.ENTITY = T1.ETCODE
AND T2.ORGID = T1.ORGID
AND T2.EID = T1.EID
AND T2.FISCAL_YR = '2005'
AND T2.ENCUMBERED_AMT > 0
AND T3.EID = T1.EID
AND T4.EID = T1.EID
AND T4.ORGID = T1.ORGID
AND J1.ORGID = 'TT'
AND T1.ORGID = J1.ORGID
AND J1.TBLGRPCD = 'THJOB'
AND T1.EJCLSS = J1.JCLASS
AND T1.EID = T5.EID    
AND T1.ORGID = T5.ORGID
--    AND T1.EFF_DATE = (SELECT MAX(T11.EFF_DATE)
--                FROM SERVER2.TECH.DBO.TRBASCTB T11
--                WHERE T11.EID = T1.EID
--                  AND T11.ORGID  = T1.ORGID
--                  AND T11.EFF_DATE <= GETDATE ())
   AND T4.EFF_DATE = (SELECT MAX(T44.EFF_DATE)
               FROM SERVER2.TECH.DBO.TRFACCTB T44
               WHERE T44.ORGID = T4.ORGID
                 AND T44.EID = T4.EID
                 AND T44.EFF_DATE <= GETDATE ())

mr_mist
Grunnio

1870 Posts

Posted - 2005-03-31 : 09:59:38

I think that the problem is because you are reference a table alias within your nested SELECT that it has no idea what it is meant to refer to. If you used the full table name you'd probably remove that error.

-------
Moo. :)

sahu74
Posting Yak Master

100 Posts

Posted - 2005-03-31 : 10:02:48

But how is it able to recognize the table alias in the nested Select in the second part? Any clues please?

PKS.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources