1 2 3 Previous Next 31 Replies Latest reply: Sep 3, 2007 1:11 AM by William Robertson Go to original post RSS
      • 30. Re: Newbie: Simple select with 'IN ALL'
        Satyaki_De
        William i've tried this solution and it shows --
        satyaki>
        satyaki>CREATE TYPE integer_tt AS TABLE OF INTEGER;
          2  /
        
        Type created.
        
        satyaki>
        satyaki>
        satyaki>WITH project AS
          2        ( select 'Carl' person, 3 id_state from dual union all
          3          select 'Carl', 4 from dual union all
          4          select 'Joe',  3 from dual union all
          5          select 'Mark', 2 from dual union all
          6          select 'Mark', 3 from dual union all
          7          select 'Mark', 4 from dual union all
          8          select 'Paul', 1 from dual union all
          9          select 'Paul', 2 from dual union all
         10          select 'Sam',  2 from dual union all
         11          select 'Sam',  3 from dual )
         12  SELECT person
         13  FROM   project
         14  GROUP  BY person
         15  HAVING integer_tt(2,3,4) SUBMULTISET OF CAST(COLLECT(id_state) AS integer_tt) ;
        HAVING integer_tt(2,3,4) SUBMULTISET OF CAST(COLLECT(id_state) AS integer_tt)
                                 *
        ERROR at line 15:
        ORA-00920: invalid relational operator
        
        
        satyaki>
        Is it because i'm using Oracle 9i, so it is giving me an error?

        Regards.

        Satyaki De.
        • 31. Re: Newbie: Simple select with 'IN ALL'
          William Robertson
          Yes, the multiset operators are a 10g feature.
          1 2 3 Previous Next