No column was specified - 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)

No column was specified

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-07-14 : 15:14:12

I'm doing a count within my nested query from my main query and I'm getting the error:


No column was specified for column 1 of 'ORD_ITEM'.

Here's my code:


SELECT 	P.ID, P.DESCRIPTION
FROM 	PARTS P
	
	LEFT OUTER JOIN
	(SELECT DISTINCT COUNT(*), MIN(O.ID)
	FROM 	ORDER O	
	WHERE O.ORDER_TYPE = 'O') ORD_ITEM  ON P.ID = ORD_ITEM.ID

WHERE 	P.ID BETWEEN @ID1 AND @ID2

I thought that since I'm using an aggregate function it would work, but not sure what's going on. Any advice?

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-07-14 : 15:18:21

You have to alias the columns in your derived table to be able to reference them


SELECT 	P.ID, P.DESCRIPTION
FROM 	PARTS P
	
	LEFT OUTER JOIN
	(SELECT DISTINCT COUNT(*) AS TheCount, MIN(O.ID) AS [ID]
	FROM 	ORDER O	
	WHERE O.ORDER_TYPE = 'O') ORD_ITEM  ON P.ID = ORD_ITEM.ID

WHERE 	P.ID BETWEEN @ID1 AND @ID2

Andy

Edit:
Do you really need the DISTINCT and COUNT(*) or is this just part of a bigger query?

Beauty is in the eyes of the beerholder

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-07-14 : 15:31:07

Thanks. That helped a lot. No I don't need the DISTINCT in there. I removed it already.

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources