Help required with alias query
From: Nick Heppleston <nickheppleston_at_gmx.co.uk>
Date: 16 Apr 2003 08:05:56 -0700
Message-ID: <150151d5.0304160705.3a57ab32_at_posting.google.com>
ERROR at line 8:
ORA-00904: invalid column name
Date: 16 Apr 2003 08:05:56 -0700
Message-ID: <150151d5.0304160705.3a57ab32_at_posting.google.com>
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 Received on Wed Apr 16 2003 - 17:05:56 CEST