2 Replies Latest reply: Mar 13, 2012 12:14 PM by Kleber M-Oracle RSS

    report on colon seperated data generated by a form using checkboxes

    Andre R
      Just created a form using checkboxes.
      When multiple checkboxes are selected data in the column is stored colon seperated.
      Table data looks like this:

      ID     GROUPS
      1     1
      2     2:1
      4     3
      3     4:21:5
      5     1:4:3
      6     5

      Now I want to build the report which will return only these rows:

      ID     GROUPS
      1     1
      2     2:1
      5     1:4:3

      What will be the best option ?
      Does APEX have a function for this ?
        • 1. Re: report on colon seperated data generated by a form using checkboxes
          Kleber M-Oracle
          Please clarify your request.
          Do you want to show row from IDs 1,2,5? Why would you need an APEX function for that? The SQL clause WHERE could restrict the options.
          • 2. Re: report on colon seperated data generated by a form using checkboxes
            Kleber M-Oracle
            Checking again... If I got this right, you want to show all rows that has the Group 1 listed.

            I would suggest 2 approaches:

            1. using clause LIKE
            with t as (
            select 1 id, '1' groups from dual
            union
            select 2, '2:1' from dual
            union
            select 4, '3' from dual
            union
            select 3, '4:21:5' from dual
            union 
            select 5, '1:4:3' from dual
            union 
            select 6, '5' from dual )
             SELECT id, groups FROM t 
             WHERE ':'||groups||':' LIKE '%:1:%'
            2. Using hierarchical SQL:
            with t as (
            select 1 id, '1' groups from dual
            union
            select 2, '2:1' from dual
            union
            select 4, '3' from dual
            union
            select 3, '4:21:5' from dual
            union 
            select 5, '1:4:3' from dual
            union 
            select 6, '5' from dual )
             SELECT id, groups FROM
              (
                     SELECT id, groups,
                   REGEXP_SUBSTR(groups, '[^:]+', 1, LEVEL) AS single_element
                     FROM  (
                          SELECT ROWNUM AS id
                          ,      groups
                          FROM   t
                        )
                     CONNECT BY INSTR(groups, ':', 1, LEVEL-1) > 0
                         AND id = PRIOR id
                         AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL 
                )
              WHERE single_element = '1'
            Both codes display same results:

            ID     GROUPS
            ---------------
            1     1
            2     2:1
            5     1:4:3

            Edited by: Kleber M on Mar 13, 2012 10:13 AM