Re: SQL Help ...

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Thu, 9 Dec 1999 11:33:19 +0100
Message-ID: <82o0d9$1455u$1_at_oceanite.cybercable.fr>

[Quoted] You can do it with a function:

create or replace function f(id varchar2) return varchar is

   result varchar2(32000) := '';
   cursor curs is

      select hcl.hcl_id, udfd.udfd_desc, udf.udf_value
      from hazardous_classes hcl, user_def_fields udf,
      user_def_field_definitions udfd
      where hcl.hcl_seq=udf.udf_fk_seq
        and udf.udf_udfd_seq=udfd.udfd_seq
        and hcl.hcl_id = id;

   first boolean := true;
begin

   for rec in curs loop

      if first then
         result := rec.udfd_desc||' '||rec.udf_value;
         first := false;
      else
         result := result||' '||rec.udfd_desc||' '||rec.udf_value;
      end if;

   end loop;
   return result;
end;
/

then:

select hcl.hcl_id, f(hcl.hcl_id)
from hazardous_classes hcl group by hcl.hcl_id;

--
Have a nice day
Michel


Daniel D. Simanaitis <dsimanaitis_at_syntra.com> a écrit dans le message :
384E770B.F59A5DC6_at_syntra.com...


> I'm hoping that somebody can help me with simple SQL query.


>

> Please send any comments / suggestions to bud_at_pfx.net as I don't

> frequent this newgroup that often.

>

> Thanks much.

>

> I  have a simple SQL query :

>

> select hcl.hcl_id, udfd.udfd_desc, udf.udf_value

> from hazardous_classes hcl, user_def_fields udf,

> user_def_field_definitions udfd

> where hcl.hcl_seq=udf.udf_fk_seq

> and udf.udf_udfd_seq=udfd.udfd_seq

>

> The output is as follows :

>

> 0001367

> Technical Shipping Name 1

> (HYDRAZINE SOLUTION 64%)

>

> 0001367

> Reportable Quantity

> 2

>

> 0001367

> IMO Placard 1

> CORROSIVE

>

> 0001367

>

> IMO Placard 2

> TOXIC

>

> 0001367

> IMO Label 1

> CORROSIVE

>

> 0001367

> IMO Label 2

> TOXIC

>

> My desired output is :

>

> 0001367 Technical Shipping Name 1 (HYDRAZINE SOLUTION 64%) Reportable

> Quantity 2 IMO Placard 1 CORROSIVE IMO Placard 2 TOXIC IMO Label 1

> CORROSIVE IMO Label 2 TOXIC

>

> Is there a way to do this via SQL ? I've tried break on, group by, order

> by and nothing seems to work.

>

Received on Thu Dec 09 1999 - 11:33:19 CET