Query help. - 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

SQL Server Development (2000)

Query help.

Author

Topic

EOJRR1
Starting Member

4 Posts

Posted - 2003-03-28 : 17:41:29

I am trying to develop a stored procedure for a web application and I am working on the query in the Query Analyzer creating the stored procedure.

I don't know if the problem has to do with the linked servers or what, but the query runs fine (with the appropriate changes) from DB2.

Here is the code


SELECT 	T1.CLASS, T1.SUPPLEMENT_TYPE, T1.TERM, T1.JOBCLASS, T1.EMPLID,
	T1.CURR_BASE_FTE, T1.ACCOUNT_NBR10, T1.POSITION_NBR, T1.XOBJECT,
	T1.ORIG_BASE_FTE, T1.ORIG_BASE_SAL, T1.CURR_BASE_SAL, T1.CURR_BASE_PCT,
	T1.CURR_BASE_BEG, T1.CURR_BASE_END, T1.DPT_INIT_BGT_FTE,
	T1.DPT_INIT_BGT_SAL, T1.PRP_BGT_SAL, T1.FISCAL_YR, T2.EMPLNAME, 
	T4.PAY_GRADE, T1.POSN_STATUS
FROM 	BUDGET.DBO.BDBPDCTB T1, 
 	[SQL2\SQL2].TECHRIS.DBO.TRBASCTB T2,
	[SQL2\SQL2].TECHRIS.DBO.TRJOBCTB T3,
	[SQL2\SQL2].TECHRIS.DBO.TRPGRCTB T4
WHERE 	T1.FISCAL_YR = '2003'
AND	T1.ACCOUNT_NBR10 = '0254456100'
AND 	T1.XOBJECT = '01'
AND 	SUBSTRING(T1.JOBCLASS,0,2) IN ('03', '04')
AND	T2.ORGID = 'TT'
AND 	T2.EMPLID = T1.EMPLID
AND 	T3.ORGID = 'TT'
AND	T3.TBLGRPCD = 'THJOB'
AND	T3.JOBCLASS = T1.JOBCLASS
AND 	T4.TBLGRPCD = 'THJOB'
AND 	T4.PAY_GRADE = T3.PAY_GRADE
AND 	T2.EFFECTIVE_DATE =
	(SELECT MAX(T22.EFFECTIVE_DATE)
	FROM 	[SQL2\SQL2].TECHRIS.DBO.TRBASCTB T22
	WHERE 	T22.EMPLID = T2.EMPLID
	AND	T22.ORGID = T2.ORGID
	AND 	T22.EFFECTIVE_DATE <= GETDATE())
AND 	T3.EFFECTIVE_DATE = 
	(SELECT MAX(T33.EFFECTIVE_DATE)
	FROM 	[SQL2\SQL2].TECHRIS.DBO.TRJOBCTB T33
	WHERE 	T33.ORGID = T3.ORGID
	AND 	T33.TBLGRPCD = T3.TBLGRPCD
	AND 	T33.JOBCLASS = T3.JOBCLASS
	AND 	T33.EFFECTIVE_DATE <= GETDATE())
AND	T4.EFFECTIVE_DATE = 
	(SELECT MAX(T44.EFFECTIVE_DATE)
	FROM 	[SQL2\SQL2].TECHRIS.DBO.TRPGRCTB T44
	WHERE 	T44.ORGID = T4.ORGID
	AND 	T44.TBLGRPCD = T4.TBLGRPCD
	AND	T44.PAY_GRADE = T4.PAY_GRADE
	AND 	T44.EFFECTIVE_DATE <= GETDATE())

however I get the following errors..


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 'T4' 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 'T4' 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 'T4' does not match with a table name or alias name used in the query.

Any Ideas ???

TIA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-28 : 17:48:39

I don't see a problem with your syntax. I know some people have had problems with copying code into Query Analyzer that certain characters are present even though you can't see them on the screen. I have never had this happen to me, but I know some people on this forum have. So open up a new window and type (don't copy) parts of the query since it's rather long:

SELECT T1.CLASS, T2.EMPLNAME, T4.PAY_GRADE, T1.POSN_STATUS
FROM BUDGET.DBO.BDBPDCTB T1,
[SQL2\SQL2].TECHRIS.DBO.TRBASCTB T2,
[SQL2\SQL2].TECHRIS.DBO.TRJOBCTB T3,
[SQL2\SQL2].TECHRIS.DBO.TRPGRCTB T4
WHERE T1.FISCAL_YR = '2003'
AND T1.ACCOUNT_NBR10 = '0254456100'

This will help narrow down the problem too if you shorten the query. Query Analyzer doesn't always give you the correct line number of the problem.

Tara

EOJRR1
Starting Member

4 Posts

Posted - 2003-03-28 : 18:45:57

Ok, this is officially DUMB... :) I went through and rebuilt the query one line at a time and found a solution that would work, but it makes no sense... the thing that seems to hold true throughout is that you cannot reference the same table within the subselect here is the "fixed" query..


SELECT 	T1.CLASS, T1.SUPPLEMENT_TYPE, T1.TERM, T1.JOBCLASS, T1.EMPLID,
	T1.CURR_BASE_FTE, T1.ACCOUNT_NBR10, T1.POSITION_NBR, T1.XOBJECT,
	T1.ORIG_BASE_FTE, T1.ORIG_BASE_SAL, T1.CURR_BASE_SAL, T1.CURR_BASE_PCT,
	T1.CURR_BASE_BEG, T1.CURR_BASE_END, T1.DPT_INIT_BGT_FTE,
	T1.DPT_INIT_BGT_SAL, T1.PRP_BGT_SAL, T1.FISCAL_YR, T2.EMPLNAME, 
	T4.PAY_GRADE, T1.POSN_STATUS
FROM 	BUDGET.DBO.BDBPDCTB T1, 
 	[SQL2\SQL2].TECHRIS.DBO.TRBASCTB T2,
	[SQL2\SQL2].TECHRIS.DBO.TRJOBCTB T3,
	[SQL2\SQL2].TECHRIS.DBO.TRPGRCTB T4
WHERE 	T1.FISCAL_YR = '2003'
AND	T1.ACCOUNT_NBR10 = '0254456100'
AND 	T1.XOBJECT = '01'
AND 	SUBSTRING(T1.JOBCLASS,0,2) IN ('03', '04')
AND	T2.ORGID = 'TT'
AND 	T2.EMPLID = T1.EMPLID
AND 	T3.ORGID = 'TT'
AND	T3.TBLGRPCD = 'THJOB'
AND	T3.JOBCLASS = T1.JOBCLASS
AND 	T4.TBLGRPCD = 'THJOB'
AND 	T4.PAY_GRADE = T3.PAY_GRADE
AND 	T2.EFFECTIVE_DATE =
	(SELECT MAX(T22.EFFECTIVE_DATE)
	FROM 	[SQL2\SQL2].TECHRIS.DBO.TRBASCTB T22
	WHERE 	T22.EMPLID = T1.EMPLID
	AND	T22.ORGID = 'TT'
	AND 	T22.EFFECTIVE_DATE <= GETDATE())
AND 	T3.EFFECTIVE_DATE = 
	(SELECT MAX(T33.EFFECTIVE_DATE)
	FROM 	[SQL2\SQL2].TECHRIS.DBO.TRJOBCTB T33
	WHERE 	T33.ORGID = 'TT'
	AND 	T33.TBLGRPCD = 'THJOB'
	AND 	T33.JOBCLASS = T1.JOBCLASS
	AND 	T33.EFFECTIVE_DATE <= GETDATE())
AND	T4.EFFECTIVE_DATE = 
	(SELECT MAX(T44.EFFECTIVE_DATE)
	FROM 	[SQL2\SQL2].TECHRIS.DBO.TRPGRCTB T44
	WHERE 	T44.ORGID = 'TT'
	AND 	T44.TBLGRPCD = 'THJOB'
	AND	T44.PAY_GRADE = T3.PAY_GRADE
	AND 	T44.EFFECTIVE_DATE <= GETDATE())

I have not tried it yet, but I would bet for some strange reason that if you want to refer back to the "main" select statement from the subselect blocks that you must join a table with a name other than that in the subselect. That is what I find is dumb... because if that is the case, what is the use of the alias???

What a problem to have on Friday... geesh... :) Thanx for the help, it started me down the right path rather than just stewing about it all weekend..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-28 : 18:49:46

You can use the same table in the subselect, but you can't use the same alias in the subselect because the subselect doesn't know about the outer select. At least that's what I can remember.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources