Column Alias Issue

[Dougeth]

Hi All,

I have having a problem conducting an aggregated function off an alias column I create on the fly. I get an error saying SearchType is an invalid column name yet when I remove the Count and Group By commands the query results in a column with the name of SearchType.

SELECT
Count(RFQ_ID), "SearchType "=
CASE
WHEN SVC_COLO = 'Y' THEN 'Colocation'
WHEN SVC_BANDWIDTH = 'Y' THEN 'Bandwidth'
WHEN SVC_MANAGED = 'Y' THEN 'Managed'
WHEN SVC_VOICE = 'Y' THEN 'Voice'
WHEN SVC_DARK_FIBER = 'Y' THEN 'Dark Fiber'
WHEN SVC_BIZ_CONT = 'Y' THEN 'Business Continuity'
END
FROM
RFQ
Where ACTIVE = 'Y'
Group By "SearchType "

[almaz]

You cannot use column aliases in the GROUP BY clause. You can either copy/paste the whole CASE statement to the GROUP BY clause or use nested SELECT statements like this (it won't hurt the performance of the query):

SELECT Count(RFQ_ID), SearchType
FROM
(
  SELECT Count(RFQ_ID), 
  SearchType =
  CASE 
     ...
     ...
  END
  FROM RFQ
  Where ACTIVE = 'Y'
) Source
Group By SearchType