SELECT: How to re-use result of complicated function ?

Hi,

  this rather looks like a SQL question but I am in an Oracle setup, so if there is an Oracle-specific solution to that I wouldn't mind, that's why I am posting here.

  I need to select from a table and perform some complicated operations of the (numerical) values in the table which will be the result of my select, for example:

SELECT Id, some_complicated_function(field1), field2 FROM my_table;

  So far so good. Now I need to select another column that also has a complicated calculation and I would like to re-use the result of some_complicated_function(field1) because it is part of the calculationt here. I would like to do this without calling some_complicated_function(field1) AGAIN because I am afraid that might slow down my performance significantly. Here is a bad example:

SELECT Id, some_complicated_function(field1), some_other_complicated_function(field3, some_complicated_function(field1)), field2
FROM my_table;

  I consider this example bad because some_complicated_function(...) will get called twice.

  I know that under certain circumstances it is possible to refer to a column that got selected just by its position, in ORDER BY for example: SELECT field1, field2 FROM my_table ORDER BY 2 ----> this will actually order by field2. So now I would like to know if there is a mechanism to refer to the result of my some_complicated_function(field1) column when calculating my other complicated column... obviously I can't use the positiob because it would be interpreted as an ordinary integer.

  Thanks a lot,

André Hartmann
:) Received on Mon Aug 30 2004 - 03:59:48 CDT