Possible to reference a column alias in a Oracle function? 1

[Slippenos]

Is there a way to reference the following column alias without using the value?

SELECT os.sourcename AS
source_name,
  om.customerid,
  COUNT(DISTINCT om.id) AS
total_count,
  nvl(SUM(od.price * od.quantity_mod),   0) AS
total_dollars,
  nvl(SUM(od.quantity_mod * pm.consunits),   0) AS
total_units,
  CASE os.abbrev
WHEN 'ED' THEN
'1'
WHEN 'EM' THEN
'2'
WHEN 'FX' THEN
'3'
WHEN 'PH' THEN
'4'
WHEN 'ML' THEN
'5'
WHEN 'OT' THEN
'6'
WHEN 'HI' THEN
'7'
END AS
reorder,
  [red]'1ORDERS RECEIVED' AS header[/red]
FROM vtrak.enum_order_source os LEFT JOIN order_main om
INNER JOIN order_details od
INNER JOIN productmaster pm ON od.productid = pm.id ON om.id = od.orderid ON os.id = om.source
 AND om.customerid = 14
 AND om.dateorder BETWEEN '12/12/2005'
 AND '12/12/2006'
 AND om.partnerid = 351
GROUP BY os.sourcename,
  om.customerid,
  CASE os.abbrev
WHEN 'ED' THEN
'1'
WHEN 'EM' THEN
'2'
WHEN 'FX' THEN
'3'
WHEN 'PH' THEN
'4'
WHEN 'ML' THEN
'5'
WHEN 'OT' THEN
'6'
WHEN 'HI' THEN
'7'
END [red]
HAVING SUBSTR(header,   1,   1) = 1
[/red]

The error message I receive is:

ORA-00904: string: invalid identifier

Which tells me the column name entered is invalid.

This query was fine in the previous MySQL environment, but not an Oracle one. Is there a way I can use this alias and not the '1ORDERS RECEIVED' value?

[blue]Go to work to learn. Don't go to work to earn.[/blue]

 

[cmmrfrds]

You can create an alias in an inline view and reference outside the view.

Select
source_name,
customerid,
reorder,
COUNT(DISTINCT id),
etc.....
From
(
SELECT os.sourcename AS
source_name,
om.customerid,
om.id,
od.quantity_mod
pm.consunits,
CASE os.abbrev
WHEN 'ED' THEN
'1'
WHEN 'EM' THEN
'2'
WHEN 'FX' THEN
'3'
WHEN 'PH' THEN
'4'
WHEN 'ML' THEN
'5'
WHEN 'OT' THEN
'6'
WHEN 'HI' THEN
'7'
END AS
reorder,
'1ORDERS RECEIVED' AS header
FROM vtrak.enum_order_source os LEFT JOIN order_main om
INNER JOIN order_details od
INNER JOIN productmaster pm ON od.productid = pm.id ON om.id = od.orderid ON os.id = om.source
AND om.customerid = 14
AND om.dateorder BETWEEN '12/12/2005'
AND '12/12/2006'
AND om.partnerid = 351
) iview
GROUP BY sourcename,
customerid,
reorder
HAVING SUBSTR(header, 1, 1) = 1

 

[Slippenos]

Ahh yes. Great idea- thanks.

[blue]Go to work to learn. Don't go to work to earn.[/blue]