3 Replies Latest reply: Feb 15, 2013 3:53 AM by 861381 RSS

    Search an ID in concatenated by stragg

    861381
      Hi there,
      Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
      PL/SQL Release 11.1.0.7.0 - Production
      Sample data
      with person as(
      select 1 as pid, 'person1' as name from dual union all
      select 2 as pid, 'person2' as name from dual union all
      select 3 as pid, 'person3' as name from dual union all
      select 4 as pid, 'person4' as name from dual union all
      select 5 as pid, 'person5' as name from dual union all
      select 6 as pid, 'person6' as name from dual
      ),
      function_type as (
      select 1 as ftid, 'function1' as name from dual union all
      select 2 as ftid, 'function2' as name from dual union all
      select 3 as ftid, 'function3' as name from dual union all
      select 4 as ftid, 'function4' as name from dual union all
      select 5 as ftid, 'function5' as name from dual union all
      select 11 as ftid, 'function11' as name from dual
      ),
      person_function_types as(
      select 1 as pid, 1 as ftid from dual union all
      select 1 as pid, 2 as ftid from dual union all
      select 1 as pid, 3 as ftid from dual union all
      select 2 as pid, 1 as ftid from dual union all
      select 2 as pid, 5 as ftid from dual union all
      select 3 as pid, 2 as ftid from dual union all
      select 3 as pid, 3 as ftid from dual union all
      select 3 as pid, 4 as ftid from dual union all
      select 4 as pid, 5 as ftid from dual union all
      select 5 as pid, 1 as ftid from dual union all
      select 6 as pid, 11 as ftid from dual
      ),
      person_function_view as(
      select p.pid,
             p.name,
             stragg(distinct ft.ftid) as function_ids,
             stragg(distinct ft.name) as function_types
      from   person_function_types pft
        left join person p
          on p.pid = pft.pid
        left join function_type ft
          on ft.ftid = pft.ftid
      group by p.name
      )
      select pfv.name,
             pfv.function_types
      from   person_function_view pfv
      where  pfv.function_ids like '%1%' 
      Problem
      The result of this query should be person all persons with function_ids containing 1.
      How ever the result of this query returns person6 containing function_id 11.
      Understandable that 11 is like %1% ... how can I avoid this?


      Thanks in advance!

      Kind regards,

      Metroickha