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.
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 );
NAME IN(P_PROFILE_VALUE );
No
rows selected
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.
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.
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,
LENGTH (REPLACE (P_PROFILE_VALUE,
‘,’)
)
+ 1);
End;
The IN Clause above query will now check for both/either ‘Profile-1’ and ‘Profile-2’ .
Recommended Posts
