How to get around using a function call in a select list-CodePudding

Currently I have the following query:

SELECT CASE WHEN TO_NUMBER (t1.value) > TO_NUMBER (MYFUNCTION (param1   => 'TEST'))
THEN 'Y' ELSE 'N' END YES_NO
FROM TABLE1 t1

I am trying to optimize this solution for performance reasons as the same thing gets called in multiple places of the large query so I am trying to move all my function calls into the WITH clause.

WITH table_values
    AS
    (MYFUNCTION (param1   => 'TEST') test_val FROM dual
     )

How can I best utilize this value in my query above? Or is there a better way to avoid function calls in the SELECT list?

CodePudding user response：

(I know you use Oracle Apex, although you didn't explicitly mentioned it here nor tagged it)

I'm a little bit confused as you're saying "select list" (is it Apex' Select List item?), and then "multiple places of the large query". Which one is it? Hopefully, it is both - you have a large query which you then use many times throughout the application as a source of page's Select List item.

If that's so, then:

WITH factoring clause seems OK to me (though, your 2nd code is wrong as it misses the select keyword)
go to Shared components and create List of Values (using that large query & WITH factoring clause)
on any page that used that query, don't set SQL Query as a source, but previously created LoV located in Shared Components

CodePudding user response：

Are you looking for something like this ?

CREATE OR REPLACE function theboss RETURN varchar2
AS
BEGIN
  RETURN 'KING';
END;
/

WITH tb (ename) AS
(
  SELECT theboss FROM dual
)
SELECT e.* 
  FROM emp e
       CROSS JOIN tb t
 WHERE e.ename = t.ename;