Column name 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

General SQL Server Forums

New to SQL Server Programming

Column name help

Author

Topic

intergalacticplanetary
Starting Member

12 Posts

Posted - 2007-02-08 : 23:11:52

Hi, i'm using column aliases to name the results of the following CASE statements and when I try to reference them in the WHERE clause i'm getting the 'Invalid Column Name' error. i can't find any reason why this shouldn't work but, alas, it doesn't. can anyone catch the problem? thanks!

SELECT DISTINCT fs.fac_id AS [Facility ID],

CASE
WHEN EXISTS (SELECT fac_id FROM fac_sic_dimension WHERE fac_id = fs.fac_id AND sic_id = 565)
THEN 1 ELSE 0
END AS [SIC 2851],

CASE
WHEN EXISTS (SELECT fac_id FROM fac_sic_dimension WHERE fac_id = fs.fac_id AND sic_id = 423)
THEN 1 ELSE 0
END AS [SIC 2869],

CASE
WHEN EXISTS (SELECT fac_id FROM fac_sic_dimension WHERE fac_id = fs.fac_id AND sic_id = 269)
THEN 1 ELSE 0
END AS [SIC 3089],

CASE
WHEN EXISTS (SELECT fac_id FROM fac_sic_dimension WHERE fac_id = fs.fac_id AND sic_id = 194)
THEN 1 ELSE 0
END AS [SIC 3471],

CASE
WHEN EXISTS (SELECT fac_id FROM fac_sic_dimension WHERE fac_id = fs.fac_id AND sic_id = 307)
THEN 1 ELSE 0
END AS [SIC 3714],

CASE
WHEN EXISTS (SELECT fac_id FROM fac_sic_dimension WHERE fac_id = fs.fac_id AND sic_id = 113)
THEN 1 ELSE 0
END AS [SIC 4911],

CASE
WHEN EXISTS (SELECT fac_id FROM fac_sic_dimension WHERE fac_id = fs.fac_id AND sic_id = 352)
THEN 1 ELSE 0
END AS [SIC 5171]

FROM fac_sic_dimension fs
WHERE [SIC 2851] <> 0 AND [SIC 2869] <> 0 AND [SIC 3089] <> 0 AND [SIC 3471] <> 0 AND
[SIC 3714] <> 0 AND [SIC 4911] <> 0 AND [SIC 5171] <> 0

ORDER BY fs.fac_id;

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-08 : 23:37:54

i'm using column aliases to name the results of the following CASE statements and when I try to reference them in the WHERE clause i'm getting the 'Invalid Column Name' error
You can't reference the alias in the where clause. You can only do it in the ORDER BY clause.

So in your where clause, you have to use the CASE WHEN .. END statement for comparison

Alternatively is to use Derived table


SELECT *
FROM
( 
SELECT DISTINCT fs.fac_id AS [Facility ID],

CASE
WHEN EXISTS (SELECT fac_id FROM fac_sic_dimension WHERE fac_id = fs.fac_id AND sic_id = 565)
THEN 1 ELSE 0
END AS [SIC 2851],

CASE
WHEN EXISTS (SELECT fac_id FROM fac_sic_dimension WHERE fac_id = fs.fac_id AND sic_id = 423)
THEN 1 ELSE 0
END AS [SIC 2869],

CASE
WHEN EXISTS (SELECT fac_id FROM fac_sic_dimension WHERE fac_id = fs.fac_id AND sic_id = 269)
THEN 1 ELSE 0
END AS [SIC 3089],

CASE
WHEN EXISTS (SELECT fac_id FROM fac_sic_dimension WHERE fac_id = fs.fac_id AND sic_id = 194)
THEN 1 ELSE 0
END AS [SIC 3471],

CASE
WHEN EXISTS (SELECT fac_id FROM fac_sic_dimension WHERE fac_id = fs.fac_id AND sic_id = 307)
THEN 1 ELSE 0
END AS [SIC 3714],

CASE
WHEN EXISTS (SELECT fac_id FROM fac_sic_dimension WHERE fac_id = fs.fac_id AND sic_id = 113)
THEN 1 ELSE 0
END AS [SIC 4911],

CASE
WHEN EXISTS (SELECT fac_id FROM fac_sic_dimension WHERE fac_id = fs.fac_id AND sic_id = 352)
THEN 1 ELSE 0
END AS [SIC 5171]

FROM fac_sic_dimension fs
WHERE [SIC 2851] <> 0 AND [SIC 2869] <> 0 AND [SIC 3089] <> 0 AND [SIC 3471] <> 0 AND
[SIC 3714] <> 0 AND [SIC 4911] <> 0 AND [SIC 5171] <> 0

) a
WHERE [SIC 2851] <> 0 AND [SIC 2869] <> 0 AND [SIC 3089] <> 0 AND [SIC 3471] <> 0 AND
      [SIC 3714] <> 0 AND [SIC 4911] <> 0 AND [SIC 5171] <> 0
ORDER BY fs.fac_id

intergalacticplanetary
Starting Member

12 Posts

Posted - 2007-02-09 : 11:33:41

Thanks KH!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-09 : 11:40:26

Test this and see if gives greater performance...

SELECT		[Facility ID],
		[SIC 2851],
		[SIC 2869],
		[SIC 3089],
		[SIC 3471],
		[SIC 3714],
		[SIC 4911],
		[SIC 5171]
FROM		(
			SELECT		Fac_ID AS [Facility ID], 
					MAX(CASE WHEN sic_id = 565 THEN 1 ELSE 0) AS [SIC 2851],
					MAX(CASE WHEN sic_id = 423 THEN 1 ELSE 0) AS [SIC 2869],
					MAX(CASE WHEN sic_id = 269 THEN 1 ELSE 0) AS [SIC 3089],
					MAX(CASE WHEN sic_id = 194 THEN 1 ELSE 0) AS [SIC 3471],
					MAX(CASE WHEN sic_id = 307 THEN 1 ELSE 0) AS [SIC 3714],
					MAX(CASE WHEN sic_id = 113 THEN 1 ELSE 0) AS [SIC 4911],
					MAX(CASE WHEN sic_id = 352 THEN 1 ELSE 0) AS [SIC 5171]
			FROM		fac_sic_dimension
			GROUP BY	Fac_ID
		) AS d
WHERE		[SIC 2851] <> 0
		AND [SIC 2869] <> 0
		AND [SIC 3089] <> 0
		AND [SIC 3471] <> 0
		AND [SIC 3714] <> 0
		AND [SIC 4911] <> 0
		AND [SIC 5171] <> 0
ORDER BY	[Facility ID]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-09 : 11:42:52

Or this, without a derived table

SELECT		Fac_ID AS [Facility ID], 
		MAX(CASE WHEN sic_id = 565 THEN 1 ELSE 0) AS [SIC 2851],
		MAX(CASE WHEN sic_id = 423 THEN 1 ELSE 0) AS [SIC 2869],
		MAX(CASE WHEN sic_id = 269 THEN 1 ELSE 0) AS [SIC 3089],
		MAX(CASE WHEN sic_id = 194 THEN 1 ELSE 0) AS [SIC 3471],
		MAX(CASE WHEN sic_id = 307 THEN 1 ELSE 0) AS [SIC 3714],
		MAX(CASE WHEN sic_id = 113 THEN 1 ELSE 0) AS [SIC 4911],
		MAX(CASE WHEN sic_id = 352 THEN 1 ELSE 0) AS [SIC 5171]
FROM		fac_sic_dimension
GROUP BY	Fac_ID
HAVING		MAX(CASE WHEN sic_id = 565 THEN 1 ELSE 0) = 1
		AND MAX(CASE WHEN sic_id = 423 THEN 1 ELSE 0) = 1
		AND MAX(CASE WHEN sic_id = 269 THEN 1 ELSE 0) = 1
		AND MAX(CASE WHEN sic_id = 194 THEN 1 ELSE 0) = 1
		AND MAX(CASE WHEN sic_id = 307 THEN 1 ELSE 0) = 1
		AND MAX(CASE WHEN sic_id = 113 THEN 1 ELSE 0) = 1
		AND MAX(CASE WHEN sic_id = 352 THEN 1 ELSE 0) = 1
ORDER BY	Fac_ID

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources