Re: SQL Help ...

This is esoteric, I know, but do you really need the first boolean? Isn't result := ''; --before the loop
and then
result := result||' '||rec.udfd_desc||' '||rec.udf_value; inside the loop sufficient.

Or I am missing something?

Lars

Michel Cadot skrev i meddelandet <82o0d9$1455u$1_at_oceanite.cybercable.fr>...
>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 Fri Dec 10 1999 - 10:04:47 CET