| Advice needed on alias query [message #6443] |
Thu, 17 April 2003 08:49  |
Nick Heppleston
Messages: 3
Registered: February 2002
|
Junior Member |
|
|
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
|
|
|
|
| Re: Advice needed on alias query [message #6446 is a reply to message #6443] |
Thu, 17 April 2003 09:48  |
Mike T
Messages: 32
Registered: August 2002
|
Member |
|
|
I'm 99% sure the Where clause is looking at the table to meet your conditions, not the Select statement, so the alias is irrelevant.
One way would be to use an inline view, but it could be slow if you table is large:
SELECT v.stbt_product, v.new_product
FROM
(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'))) v
WHERE v.new_product = '1'
GROUP BY v.stbt_product;
Another way would be to repeat your function in the Where clause, but I'm not sure if you can use functions in a Where clause:
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 midw.IS_PRODUCT_NEW(MIN(stbt_recdate)) = '1'
GROUP BY stbt_product;
Also, I don't really see the need for the Group By. I think you want to use DISTINCT. I'm sure there are other solutions, but I hope that helps a little...
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
