I have an SSRS report with a parameter named Responsible Department, that allows for multiple selection of values. For example: electrical, mechanical, shutdown. These values need to be split into as individual records in one column as shown below.
How do we do this in Oracle? The answer is to use the regexp_substr function, introduced in Oracle 10g.
The query below shows how the Responsible Department parameter values are split. Note that there is no space in between the comma and Shutdown.
DECLARE @ResponsibleDepartment VARCHAR(MAX) = ‘Electrical,Shutdown’
DECLARE @Statement VARCHAR(MAX)
= ‘
SELECT regexp_substr(‘ + ”” + @ResponsibleDepartment + ”” + ‘,’ + ”” + ‘[^,]+’ + ”” + ‘, 1, level) as RESPONSIBLE_DEPARTMENT from dual
CONNECT BY regexp_substr(‘ + ”” + @ResponsibleDepartment + ”” + ‘,’ + ”” + ‘[^,]+’ + ”” + ‘, 1, level) is not null
‘;
EXEC (@Statement) AT BABEL
The query below shows how to pass them into a table. Note that there is a space in between the comma and Shutdown, hence the need to use the LTRIM function to remove the white space returned from the regexp_substr function.
DECLARE @ResponsibleDepartment VARCHAR(MAX) = ‘Electrical, Shutdown’
DECLARE @Statement VARCHAR(MAX)
= ‘
SELECT *
FROM EVENT_CODE
WHERE RESPONSIBLE_DEPARTMENT IN ( SELECT LTRIM(regexp_substr(‘ + ”” + @ResponsibleDepartment + ”” + ‘,’ + ”” + ‘[^,]+’ + ”” + ‘, 1, level)) from dual
CONNECT BY regexp_substr(‘ + ”” + @ResponsibleDepartment + ”” + ‘,’ + ”” + ‘[^,]+’ + ”” + ‘, 1, level) is not null
)
‘;
EXEC (@Statement) AT BABEL
To pass the multiple values of the parameter from SSRS into the SQL query, these values need to be concatenated with a space and comma. The parameter in the dataset containing the query needs to be set to below, otherwise the dataset will fail to run.
= Join(Parameters!ResponsibleDepartment.Value, ” , “)
ChinChin's space 