ORACLE question about identifiers

If you create a function and name it is there any way to use that name later in your statement?

For example in the code below I named the sellprice-buyprice as PROFIT but I can't seem to use that again as it errors as an invalid identifier. If I can't do that, please let me know how I would display the max profit entry in that column开发者_StackOverflow中文版.

SELECT item, buyprice, sellprice,
sellprice-buyprice as “PROFIT”
FROM auctions
WHERE PROFIT = (select MAX(PROFIT) from auctions); 

Could you try this statement: select * from (select item, buyprice, sellprice, sellprice-buyprice as “PROFIT” from auctions order by 4 desc) where rownum = 1;

You would use the function name in the where clause, referencing the alias is not valid there.

SELECT item, buyprice, sellprice,
sellprice-buyprice as “PROFIT”
FROM auctions
WHERE sellprice-buyprice = (select MAX(PROFIT) from auctions);

Or you can do the following if you really want to use the alias

select item, buyprice, sellprice, profit
from
(
  SELECT item, buyprice, sellprice, sellprice-buyprice as “PROFIT”
  FROM auctions)
)
WHERE PROFIT = (select MAX(PROFIT) from auctions);

In addition to using subqueries, as has already been suggested, you could achieve this through subquery refactoring:

WITH auctions_p AS (SELECT   item,
                             buyprice,
                             sellprice,
                             sellprice - buyprice AS profit
                      FROM   auctions)
SELECT   item,
         buyprice,
         sellprice,
         profit
  FROM   auctions_p
 WHERE   profit = (SELECT   MAX(profit) FROM auctions_p);

If you're going to use this a lot and you're using 11g, you could make this calculation permanently available by defining it as a virtual column:

ALTER TABLE auctions ADD (profit AS (sellprice - buyprice));

SELECT   item,
         buyprice,
         sellprice,
         profit
  FROM   auctions
 WHERE   profit = (SELECT   MAX(profit) FROM auctions);