Parsing Comma Separated string in WHERE Clause of SQL Statement in Oracle Forms

Description:

In some cases, we get a comma-separated string as output (say from another select statement or block) that we would need to pass to the IN clause of a Select statement.

Requirement :

SQL Statement needs to process a String value (having multiple Text values separated by Commas) in the WHERE Clause at runtime.

This String value is generated dynamically at runtime based on specific criteria and processing logic.

Illustration:

Oracle Forms example

 

1. Setting Profile_value based on specific condition.

 

 BEGIN

   if <<Condition1 = True>>  

   then

            P_PROFILE_VALUE := ‘Profile-1, Profile-2’;

   

    Elsif <<Condition2 = True>>  

    then

            P_PROFILE_VALUE := ‘Profile-3, Profile-4’;   

    Else

            P_PROFILE_VALUE := ‘Profile-5’;   

    End If;

 

Now, assuming the  Condition1 = True then we will have the string as below :

      P_PROFILE_VALUE := ‘Profile-1, Profile-2’;

 

 

2. If the above value is passed to an IN Condition of a WHERE clause, it would be treated as a SINGLE string of value ‘Profile-1, Profile-2′  and the query will Not check for costs Profile-1 and Profile-2 independently (i.e.) it would fetch records that have the value ‘Profile-1, Profile-2′.

 

Select VALUE  from xx_test_table where NAME  IN(P_PROFILE_VALUE );

 

No rows selected

 

 

3. The desired result can be achieved by splitting the comma-separated string to individual strings and pass it to the IN clause.

 

Oracle provides regexp_substr  function, which comes handy for this scenario.

 

SELECT  REGEXP_SUBSTR(‘Profile-1,Profile-2’,

‘[^,]+’,

1,

LEVEL

)

FROM DUAL

CONNECT BY LEVEL <= LENGTH (‘Profile-1,Profile-2’)

– LENGTH (REPLACE (‘Profile-1,Profile-2’,

‘,’) )  + 1

 

Output:

Profile-1

Profile-2

 

 

4. Substitute the above logic in query as shown below to get the desired results :

 

Select VALUE  from xx_test_table where NAME  IN

(SELECT  REGEXP_SUBSTR(P_PROFILE_VALUE,

‘[^,]+’,

1,

LEVEL

)

FROM DUAL

CONNECT BY LEVEL <=

LENGTH (P_PROFILE_VALUE)

– LENGTH (REPLACE (P_PROFILE_VALUE,

‘,’)

)

+ 1);

 

End;

 

The IN Clause above query will now check for both/either ‘Profile-1’ and ‘Profile-2’.

 

 

Summary:

This Post explained what the steps are should follow for create separated string in WHERE Clause of SQL Statement in Oracle Forms

Queries?

Do drop a note by writing us at contact@doyensys.com or use the comment section below to ask your questions.