Invalid Column Name with Year function and Derived Tables

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)

Invalid Column Name with Year function and Derived Tables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-04 : 09:14:38

Pina writes "Hi There!

I am trying to group several columns of data in a SQL 2000 table by year using the stored procedure provided below. The error I get is "Invalid Column Name: dt_yy". Any ideas what the problem may be?

Your help is much appreciated. Thanks in advance!

CREATE PROCEDURE csp_reportCustomYear
@start datetime='1980/01/01',
@end datetime='2200/01/01',
@shift varchar(50),
@line varchar(50),
@cell varchar(50)

AS

SELECT a1.dt_yy,
a1.line_description,
a1.cell_description,
dt_durationSum,
dt_freqCount

FROM
(SELECT YEAR(a.dt_StartTimestamp)as dt_yy,
a.line_description,
a.cell_description,
SUM(dt_duration) AS dt_durationSum
FROM v_hisDowntime a
WHERE dt_StartTimestamp BETWEEN @start AND @end
GROUP BY a.dt_yy, a.line_description, a.cell_description)AS a1

JOIN
(SELECT YEAR(a.dt_StartTimestamp)as dt_yy,
a.line_description,
a.cell_description,
COUNT(dt_InitialRecord) AS dt_freqCount
FROM v_hisDowntime a
WHERE dt_StartTimestamp BETWEEN @start AND @end
AND dt_InitialRecord = 1
GROUP BY a.dt_yy, a.line_description, a.cell_description) AS b1
ON a1.dt_yy = b1.dt_yy
AND a1.line_description = b1.line_description
AND a1.cell_description = b1.cell_description

GROUP BY a1.dt_yy, a1.line_description, a1.cell_description, dt_durationSum, dt_freqCount
ORDER BY a1.dt_yy, a1.line_description, a1.cell_description

GO"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-12-04 : 09:37:03

I'd hate to say that this was the reason, but it could be because you didn't include a space between the word "AS" and the column expression:

CREATE PROCEDURE csp_reportCustomYear
@start datetime='1980/01/01',
@end datetime='2200/01/01',
@shift varchar(50),
@line varchar(50),
@cell varchar(50)

AS

SELECT a1.dt_yy,
a1.line_description,
a1.cell_description,
dt_durationSum,
dt_freqCount

FROM
(SELECT YEAR(a.dt_StartTimestamp) as dt_yy,
a.line_description,
a.cell_description,
SUM(dt_duration) AS dt_durationSum
FROM v_hisDowntime a
WHERE dt_StartTimestamp BETWEEN @start AND @end
GROUP BY a.dt_yy, a.line_description, a.cell_description) AS a1

JOIN
(SELECT YEAR(a.dt_StartTimestamp) as dt_yy,
a.line_description,
a.cell_description,
COUNT(dt_InitialRecord) AS dt_freqCount
FROM v_hisDowntime a
WHERE dt_StartTimestamp BETWEEN @start AND @end
AND dt_InitialRecord = 1
GROUP BY a.dt_yy, a.line_description, a.cell_description) AS b1
ON a1.dt_yy = b1.dt_yy
AND a1.line_description = b1.line_description
AND a1.cell_description = b1.cell_description

GROUP BY a1.dt_yy, a1.line_description, a1.cell_description, dt_durationSum, dt_freqCount
ORDER BY a1.dt_yy, a1.line_description, a1.cell_description

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-04 : 10:10:46

Really? Surely the lexer won't tokenize it any differently?

Edited by - Arnold Fribble on 12/04/2001 10:11:10

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-12-04 : 11:03:32

I don't know for sure, it's just a guess, but it wouldn't surprise me if it had problems. Technically speaking it's a syntax error.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources