Issue - Multiple function call in SELECT statement

[aaloksharma08]

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.

Regards,
Aalok

[LKBrwn_DBA]

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). . . .

I do not believe it's executing 8 times:

SQL> CREATE OR REPLACE FUNCTION Derive_Eq_Jf_Jl_Exec_Flag
  2  (
  3     P1    VARCHAR2
  4   , P2       VARCHAR2
  5  )
  6     RETURN VARCHAR2
  7  IS
  8     V1         VARCHAR2 ( 100 );
  9     V2         VARCHAR2 ( 100 );
 10     V_Return   VARCHAR2 ( 100 );
 11     V_Wk       VARCHAR2 ( 512 );
 12  BEGIN
 13     DBMS_RANDOM.Seed ( P2 || P2 );
 14     V_Wk   := TRIM ( DBMS_RANDOM.String ( 'X', 9 ) );
 15
 16     WHILE LENGTH ( V_Wk ) < 9
 17     LOOP
 18        V_Wk   := TRIM ( DBMS_RANDOM.String ( 'X', 9 ) ) || V_Wk;
 19     END LOOP Return;
 20
 21     RETURN    SUBSTR ( V_Wk, 1, 3 )
 22            || '|'
 23            || SUBSTR ( V_Wk, 4, 3 )
 24            || '|'
 25            || SUBSTR ( V_Wk, 7, 3 );
 26  END;
 27  /

Function created.

SQL>  SELECT X.input_val,
  2         Substr (X.input_val, 1, Instr (X.input_val, '|', 1, 1) - 1)            AS "EQ_JOB_FUNCTION",
  3         Substr (X.input_val, Instr ( X.input_val, '|', 1, 1 ) + 1, (
  4         Instr (X.input_val, '|', 1, 2) - Instr (X.input_val, '|', 1, 1) - 1 )) AS "EQ_JOB_LEVEL",
  5         Substr (X.input_val, Instr ( X.input_val, '|', 1, 2 ) + 1)             AS "EQ_EXEC_IT_NON_IT_FLAG"
  6  FROM   (SELECT Derive_eq_jf_jl_exec_flag ('CISOJO', 'MIS/IT') AS "INPUT_VAL"
  7          FROM   dual) X
  8  /

INPUT_VAL
--------------------------------------------------------------------------------
EQ_JOB_FUNCTION
--------------------------------------------------------------------------------
EQ_JOB_LEVEL
--------------------------------------------------------------------------------
EQ_EXEC_IT_NON_IT_FLAG
--------------------------------------------------------------------------------
CA2|SB3|A8Q
CA2
SB3
A8Q


SQL>

[aaloksharma08]

Thanks for the reply LKBrwn_DBA.

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.

[LKBrwn_DBA]

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.