2 Replies Latest reply: Feb 7, 2013 12:20 PM by user554531 RSS

    How to select distinct on nested table defined on object type

    user554531
      create or replace
      type diag_code_table as table of diag_code_rec

      create or replace
      TYPE diag_code_rec as object
      (
      rec_text_href varchar2(4000),
      rec_text_href1 CLOB,
      rec_source_code varchar2(10),
      rec_description varchar2(300),
      rec_scenario varchar2(4000),
      rec_scenario1 CLOB,
      rec_flag Varchar2(5),
      rec_destination_code varchar2(10),
      rec_des_description varchar2(300),
      rec_icd9_pdflk varchar2(10),
      rec_icd10_pdflk varchar2(10));

      l_loop_diag_code_table1 := DIAG_CODE_TABLE();

      SELECT CAST(MULTISET(SELECT rec_text_href,
                     rec_text_href1,
                     rec_source_code,
                     rec_description,
                     rec_scenario ,
                     rec_scenario1 ,
                     rec_flag,
                     rec_destination_code ,
                     rec_des_description,
                     rec_icd9_pdflk,
                     rec_icd10_pdflk
           FROM TABLE(l_loop_diag_code_table1 )
           ORDER BY rec_source_code ASC, rec_destination_code ASC ) as DIAG_CODE_TABLE)
      INTO l_loop_diag_code_table1
      FROM DUAL;

      I need to get distinct rec_source_code records into l_loop_diag_code_table1

      Distinct keyword is not allowed since rec_text_href1,rec_scenario1 are clob fields.

      COuld anyone please suggest how to solve this?

      Thanks,
      in advance
        • 1. Re: How to select distinct on nested table defined on object type
          IvanBlanarik
          Hi,
          you have to define an order method for the object - that enables to sort data and filter duplicates.
          This is a simplified version that works:
          CREATE OR REPLACE TYPE diag_code_rec AS object
            (
              rec_text_href VARCHAR2(4000),
              rec_text_href1 CLOB,
              ORDER MEMBER FUNCTION equals(object diag_code_rec) RETURN NUMBER
            ) ;
              
          CREATE OR REPLACE TYPE body diag_code_rec
          AS
            ORDER MEMBER FUNCTION equals(object diag_code_rec) RETURN NUMBER
          IS
          BEGIN
            IF self.rec_text_href < object.rec_text_href THEN
              RETURN 1;
              IF self.rec_text_href > object.rec_text_href THEN
                RETURN - 1;
              ELSIF dbms_loc.compare(self.rec_text_href1 object.rec_text_href1) < 1 THEN
                RETURN 1;
              ELSIF dbms_loc.compare(self.rec_text_href1 object.rec_text_href1) > 1 THEN
                RETURN 1;
              ELSE
                RETURN 0;
              END IF;
            END equals;
          END; 
          
          create or replace type diag_code_table as table of diag_code_rec;
          
          SET serveroutput ON;
          DECLARE
            l_loop_diag_code_table1 DIAG_CODE_TABLE := DIAG_CODE_TABLE(
             diag_code_rec('aa', to_clob('aa')), 
             diag_code_rec('aa', to_clob('bb')), 
             diag_code_rec('aa', to_clob('aa')), 
             diag_code_rec('aa', to_clob('cc'))
             ) ;
             l_loop_diag_code_table2 DIAG_CODE_TABLE;
          BEGIN
            dbms_output.put_line('Before distinct: '||l_loop_diag_code_table1.count) ;
            SELECT DISTINCT diag_code_rec(rec_text_href, rec_text_href1)
            BULK COLLECT INTO l_loop_diag_code_table2
            FROM TABLE(l_loop_diag_code_table1) ;
            dbms_output.put_line('After distinct: '||l_loop_diag_code_table2.count) ;
          END;
          /
          • 2. Re: How to select distinct on nested table defined on object type
            user554531
            Thankyou for posting the reply.

            I will try the solution.