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

On Mon, 30 Aug 2004 12:05:10 +0200, "Michel Cadot" <micadot{at}altern{dot}org> wrote:

>
>"André Hartmann" <andrehartmann_at_hotmail.com> a écrit dans le message de
>news:4132ec84$1_at_olaf.komtel.net...
>> 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
>> :)
>>
>>
>
>Just an idea, put it in a subquery:
>
>select id, complicated1, some_other_complicated_function(field3, complicated1), field2
>from (id, some_complicated_function(field1) conplicated1, field2, field3 from my_table);

Which is, of course, not a subquery but an inline view. However, if the complicated function is issuing selects the OP will have a problem *without* using this tric.

--
Sybrand Bakker, Senior Oracle DBA