Issue - Multiple function call in SELECT statement
Hi folks,
Need a SQL/PLSQL related help. Please see the below SQL query. In this query, I am invoking a function "DERIVE_EQ_JF_JL_EXEC_FLAG", which returns a string value. That string contains 3 individual values separated by pipe delimter, which I further split into individual values using combinations of SUBSTR and INSTR functions. One sample value returned by the function "DERIVE_EQ_JF_JL_EXEC_FLAG" is "ABC|OPR|XYZ".
select
SUBSTR(x.INPUT_VAL, 1, INSTR(x.INPUT_VAL, '|', 1, 1)-1) as "EQ_JOB_FUNCTION",
SUBSTR(x.INPUT_VAL, INSTR(x.INPUT_VAL, '|', 1, 1)+1, (INSTR(x.INPUT_VAL, '|', 1, 2)-INSTR(x.INPUT_VAL, '|', 1, 1)-1) ) as "EQ_JOB_LEVEL",
SUBSTR(x.INPUT_VAL, INSTR(x.INPUT_VAL, '|', 1, 2)+1) as "EQ_EXEC_IT_NON_IT_FLAG"
from (
select DERIVE_EQ_JF_JL_EXEC_FLAG('CISOJO', 'MIS/IT') as "INPUT_VAL" from dual
) x;
Now the problem is when I fire this query, the function "DERIVE_EQ_JF_JL_EXEC_FLAG" is getting executed 8 times (i.e. the number of times "x.INPUT_VAL" is being referenced in the query). What I want is to rewrite this query in such a manner that it executes the function "DERIVE_EQ_JF_JL_EXEC_FLAG" only once, and then just use its output multiple times in SUBSTR and INSTR functions.
Is there any way to achieve that in one single SELECT statement? Please do let me know if the above mentioned issue/requirements are unclear.
Hi folks,
. . . e t c . . .
Now the problem is when I fire this query, the function "DERIVE_EQ_JF_JL_EXEC_FLAG" is getting executed 8 times (i.e. the number of times "x.INPUT_VAL" is being referenced in the query). . . .
Let me also share with you the sample function code, as well as the SELECT statement, as well as the dbms_output screenshot. If you have a look at the screenshot, you will see the dbms_output statement getting printed 8 times, which shows that the function itself got fired 8 times.
Function code:
CREATE OR REPLACE FUNCTION DERIVE_EQ_JF_JL_EXEC_FLAG_TMP (
i_JOB_TITLE VARCHAR2,
i_JOB_TITLE_CLASS VARCHAR2)
RETURN VARCHAR2
AS
return_value_concatenated VARCHAR2 (400);
BEGIN
return_value_concatenated := 'ABC|OPR|XYZ';
dbms_output.put_line ('return_value_concatenated -- ' || return_value_concatenated);
RETURN return_value_concatenated;
END;
/
SELECT statement:
select
SUBSTR(x.INPUT_VAL, 1, INSTR(x.INPUT_VAL, '|', 1, 1)-1) as "EQ_JOB_FUNCTION",
SUBSTR(x.INPUT_VAL, INSTR(x.INPUT_VAL, '|', 1, 1)+1, (INSTR(x.INPUT_VAL, '|', 1, 2)-INSTR(x.INPUT_VAL, '|', 1, 1)-1) ) as "EQ_JOB_LEVEL",
SUBSTR(x.INPUT_VAL, INSTR(x.INPUT_VAL, '|', 1, 2)+1) as "EQ_EXEC_IT_NON_IT_FLAG"
from (
select DERIVE_EQ_JF_JL_EXEC_FLAG_TMP('input1', 'input2') as "INPUT_VAL" from dual ) x
dbms_output screenshot file is attached along with this reply.
Unfortunately, i don't have the liberty to remove the inline view, because of the way the output of this function is going to be used in the existing scheme of things.
Thanks for the reply LKBrwn_DBA.
. . . e t c
dbms_output screenshot file is attached along with this reply.
Unfortunately, i don't have the liberty to remove the inline view, because of the way the output of this function is going to be used in the existing scheme of things.
What inline view?
All I see is that the query returns one (1) row and you executed the query eight (8) times.
Each time you execute, the dbms_output result appends to the buffer, which is the expected result in TOAD.
If you notice on the DBMS Output tab there in an icon like a pencil eraser, click on it before executing the query and you get only the results for that execution.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb