Friday 9 January 2015

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

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 need to process a String value (having multiple Text values seperated by Commas) in the WHERE Clause at runtime. 

This String value is generated dynamically at run-time based on certain criteria and processing logic.


Illustration:
Oracle Forms example

1.  Setting Profile_value based on certain 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 a 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 values Profile-1 and Profile-2 independently (ie) 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' .



No comments:

Post a Comment