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

"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);

-- 
Regards
Michel Cadot