force subquery resolution first

Question

I'm creating a query which uses 2 embedded server functions multiple times.

Problem: the functions search through a decently large table, and they take a long time to execute.

Goal: Use a subquery as if it were a table so that I can reference columns without running the function to generate the column more than once.

Example Pseudocode:

Select general.column1, general.column2, general.column1-general.column2
from (select package.function1('I take a long time') column1, 
      package.function2('I take even longer') column2,
      normal_column
      from bigtable) general;

When I run my code general.column1 will reference the function in the statement of column1, not the data returned by it (which is ultimately what I'm after).

I'm fairly new to SQL, so any help is appreciated and if you need more info, I'll do my best to provide it.

Thanks!

Answer 1

I suggest you tu use the subquery factoring. The first subquery will be executed only once and then used through the rest of he query.

WITH function_result AS
  (SELECT package.function1('I take a long time') column1 
   ,      package.function2('I take even longer') column2
   FROM   dual)
SELECT function_result.column1
,      function_result.column2
,      function_result.column1 - function_result.column2
,      bigtable.normal_column
FROM   bigtable