Re: Help required with alias query

From: Frank <fvanbortel_at_netscape.net>
Date: Thu, 17 Apr 2003 20:25:50 +0200
Message-ID: <3E9EF1AE.4060806_at_netscape.net>

Nick Heppleston wrote:
> I have the following query:
>
> SELECT
> stbt_product,
> midw.IS_PRODUCT_NEW(MIN(stbt_recdate)) AS new_product
> FROM
> stbatch
> WHERE
> ((stbt_loc = 'SALT') OR (stbt_loc = 'CHER') OR (stbt_loc =
> 'CHER')) AND
> new_product = '1'
> GROUP BY
> stbt_product;
>
> and assign the result of the IS_PRODUCT_NEW function to the alias
> 'new_product'. The return value of the fuction is either a 1 or 0
> (numeric not char).
>
> Unfortunately, the WHERE...new_product = '1' doesn't work. From
> SQL*Plus I get the following error:
>
> new_product = '1'
> *
> ERROR at line 8:
> ORA-00904: invalid column name
>
> I have read that I can only use an alias in the order by clause -
> could anyone shed any light on how I could approach this problem given
> the sticking points i've come across - I only want to limit the
> resultant data set to be those records where the IS_PRODUCT_NEW
> function returns '1'.
>
> Regards, Nick
WHERE ((stbt_loc = 'SALT') OR

	(stbt_loc = 'CHER') OR
  	(stbt_loc = > 'CHER'))

AND midw.IS_PRODUCT_NEW(MIN(stbt_recdate)) = 1

should work - it's in the where clause, not order by. And lose the quotes

-- 
Regards, Frank van Bortel

Received on Thu Apr 17 2003 - 20:25:50 CEST