Re: Help required with alias query

nickheppleston_at_gmx.co.uk (Nick Heppleston) wrote in message news:<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

If you only want to use 1 from function return, then you can do this,

SELECT
          stbt_product, '1'
...
...
WHERE
       ((stbt_loc = 'SALT') OR (stbt_loc = 'CHER') OR (stbt_loc = 'CHER')) AND
          midw.IS_PRODUCT_NEW(MIN(stbt_recdate)) = '1' ..

Regards
/Rauf Sarwar Received on Thu Apr 17 2003 - 23:14:25 CEST