Invalid column on SELECT - 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 2005 Forums

Transact-SQL (2005)

Invalid column on SELECT

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-07-31 : 11:30:12

I have this query that runs as below with the SDATE and STIME lines commented out. As soon as I comment them in, I get an Invalid Column error. The derived columns for HighUpload and HighDownload work fine. What is wrong with SDATE and STIME?

SELECT
	SYNCH_LOG_ID
	, OPERATOR_NAME
	, STATION_NAME
	, START_DATE_TIME
--	, CONVERT(varchar(10), START_DATE_TIME, 101) SDATE
--	, CONVERT(varchar(10), START_DATE_TIME, 8) STIME
	, END_DATE_TIME
	, SYNCH_TYPE
	, DUR
	, SYNC_MODE
	, COUNT(DISTINCT PT_COUNT) PT_CT
	, SUM(PTS_UP) P_UP
	, SUM(PTS_DN) P_DN
	, SUM(RECS_UP) R_UP
	, SUM(RECS_DOWN) R_DN
	, CONF
	, CASE WHEN SUM(PTS_UP) >25 THEN 'HIGH UPLOAD' ELSE '' END HIGH_UPLOAD
	, CASE WHEN SUM(PTS_DN) >25 THEN 'HIGH DOWNLOAD' ELSE '' END HIGH_DOWN
FROM #T
GROUP BY
	SYNCH_LOG_ID
	, OPERATOR_NAME
	, STATION_NAME
	, START_DATE_TIME
--	, SDATE
--	, STIME
	, END_DATE_TIME
	, SYNCH_TYPE
	, DUR
	, SYNC_MODE
	, CONF
ORDER BY DUR

Duane

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-31 : 11:48:28

Try this

SELECT
	SYNCH_LOG_ID
	, OPERATOR_NAME
	, STATION_NAME
	, START_DATE_TIME
	, CONVERT(varchar(10), START_DATE_TIME, 101) SDATE
	, CONVERT(varchar(10), START_DATE_TIME, 8) STIME
	, END_DATE_TIME
	, SYNCH_TYPE
	, DUR
	, SYNC_MODE
	, COUNT(DISTINCT PT_COUNT) PT_CT
	, SUM(PTS_UP) P_UP
	, SUM(PTS_DN) P_DN
	, SUM(RECS_UP) R_UP
	, SUM(RECS_DOWN) R_DN
	, CONF
	, CASE WHEN SUM(PTS_UP) >25 THEN 'HIGH UPLOAD' ELSE '' END HIGH_UPLOAD
	, CASE WHEN SUM(PTS_DN) >25 THEN 'HIGH DOWNLOAD' ELSE '' END HIGH_DOWN
FROM #T
GROUP BY
	SYNCH_LOG_ID
	, OPERATOR_NAME
	, STATION_NAME
	, START_DATE_TIME
	, CONVERT(varchar(10), START_DATE_TIME, 101)
	, CONVERT(varchar(10), START_DATE_TIME, 8)
	, END_DATE_TIME
	, SYNCH_TYPE
	, DUR
	, SYNC_MODE
	, CONF
ORDER BY DUR

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-07-31 : 17:28:23

Thank you. I think I finally got it to run by eliminating the fields from the group by, but not the select. But I will keep your idea in mind for the future.

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources