SQL & PL/SQL

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

Hello,

This is a new request from a query Frank helped me with: https://community.oracle.com/message/12506306#12506306

The request is to all the user to restrict the report based on a Multi-Select Filter on the Front End (APEX application).

The Filter returns a Colon Delimited list and the data in the table can be saved in a Colon Delimited list too. There can be 1 to n values saved (up to 4000 characters).

I need a WHERE clause that will allow a match, if it exists, between the data in the Table Column and the Mult-Select List filter.

The shortened query for this post would be something like:

SELECT slt_id, slt_item, slt_owner

FROM slt_dashboard

WHERE slt_owner IN (colon delimited list from the page);

The Create Table and Insert statements:

CREATE TABLE slt_dashboard
(
  dashboard_id  NUMBER,
  slt_id        VARCHAR2(10),
  slt_item      VARCHAR2(2500),
  slt_owner     VARCHAR2(4000),
  slt_type      VARCHAR2(25),
  slt_year      NUMBER,
  parent_id     NUMBER
);

--Insert slt_dashboard
INSERT INTO slt_dashboard(dashboard_id,slt_id,slt_item,slt_owner,slt_type,slt_year,parent_id)
VALUES (12,'1.1','Implement revenue enhancement initiatives','E15889:JPARISI:BDUR63','Business Commitment',2014,6);

INSERT INTO slt_dashboard(dashboard_id,slt_id,slt_item,slt_owner,slt_type,slt_year,parent_id)
VALUES (13,'1.2','Strengthen our Energy position','KVROMAN','Business Commitment',2014,6);

I have a Function to separate the Colon Delimited List into an array for the IN clause, but since these values can be saved in a Colon Delimited List in the Table Column, I'm having trouble being able to parse the column data AND the Filter data.

The Function is:

CREATE OR REPLACE FUNCTION get_list( p_string IN VARCHAR2
                                    ,p_delimiter IN VARCHAR2 DEFAULT ':'
                                   )
    RETURN vc_array_1
    PIPELINED
IS
    l_string VARCHAR2( 32000 );
    l_array wwv_flow_global.vc_arr2;
BEGIN
    l_array     :=
        APEX_UTIL.string_to_table( p_string
                                  ,p_delimiter
                                 );

    FOR i IN l_array.FIRST .. l_array.LAST
    LOOP
        PIPE ROW ( TRIM( l_array( i ) ) );
    END LOOP;

    RETURN;
END;

The Function would be called in the query as (this is just for reference in case someone wanted to know):

SELECT ...
FROM ...
WHERE slt_owner IN (SELECT * FROM TABLE( get_list( :P115_SLT_OWNER ) ));

But I can't use this approach because the Table data can be saved in a Colon Delimited List too.

Desired Output:

If the Mult-Select List filter contains: E15889:JPARISI then

1.1 Implement revenue enhancement initiatives E15889;JPARISI (that's a semi-colon between the names)

If the Multi-Select List filter contains: KVROMAN then

1.2 Strengthen our Energy position KVROMAN

If the Multi-Select List filter contains: BDUR63:KVROMAN then

1.1 Implement revenue enhancement initiatives BDUR63

1.2 Strengthen our Energy position KVROMAN

Please let me know if something isn't clear.

Thanks,

Joe