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