Re: How do I construct an select statement to do this view...

From: <oratune_at_aol.com>
Date: 2000/07/12
Message-ID: <8kilc5$47i$1@nnrp1.deja.com>#1/1

In article <396CCAA3.A4E71C61_at_hotmail.com>,   Stephen Hurrell <hurrells_at_hotmail.com> wrote:
> This is a multi-part message in MIME format.
> --------------1B32BD9FCE5DD4D20C818497
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> Hello.
>
> I have a table#1 that has the following structure with all fields NOT
> NULL.
>
> create table items (
> thekey varchar2(10)
> ,status varchar2(2)
> ,value number(1)
> ) ;
>
> Here is some sample data (the real table has 5-9 million rows..)
>
> 100636 OP 5
> 100636 CL 2
> 100636 ZX 2
> 100636 PL 1
> 123555 OP 4
> 123555 CL 5
> 123555 ZX 1
> 333222 OP 2
> 333222 CL 8
>
> What is the most efficient way to report the data as follows;
>
> thekey thereportstring
> 100636 OP5CL2ZX2PL1
> 123555 CL5OP4ZX1
> 333222 CL8OP2
>
> Here are the requirements and assumptions.
>
> - For any given key there can be 1 or more rows of data.
> - For any given key I want the status with the largest value first in
> field thereportstring down to the smallest value last.
> - The status field can have upto 20 different values and more get
 added
> to the foreign key.
> - I plan to make this a procedure but pl/sql or sql is OK for now.
> - I will have to make this bi-directional later as I will be getting
> data in both formats but not important now.
>
> Finally I considered having the data table#2 like this, using zero (0)
> for unused values, but it is too sparse, too big and very slow but the
> SQL is trivial to write.
>
> create sparce_items (
> thekey varchar2(10)
> ,status_op number(1) not null
> ,status_cl number(1) not null
> ,status_zx number(1) not null
> ,status_pl number(1) not null
>
> ...and so on for 20 or so different status values....
>
> ) ;
>
> And so I am considering getting to my report from table#1 via a view
> like table#2.
>
> There has to be a better way....
>
> STeve
>
> --------------1B32BD9FCE5DD4D20C818497
> Content-Type: text/x-vcard; charset=us-ascii;
> name="hurrells.vcf"
> Content-Transfer-Encoding: 7bit
> Content-Description: Card for Stephen Hurrell
> Content-Disposition: attachment;
> filename="hurrells.vcf"
>
> begin:vcard
> n:Hurrell;Stephen
> tel;fax:807-343-7445
> tel;work:807-343-7427
> x-mozilla-html:TRUE
> org:Office of the Registrar General
> adr:;;189 Red River Road;Thunder Bay;Ontario;P7B 6L8;Canada
> version:2.1
> email;internet:hurrells_at_ccr.gov.on.ca
> title:Sr. Technical Coordinator
> x-mozilla-cpt:;23072
> fn:Stephen Hurrell
> end:vcard
>
> --------------1B32BD9FCE5DD4D20C818497--
>
>

Try this:

declare

     cursor rep_dta is
     select thekey, status, value
     from items
     order by thekey asc, value desc;
     prev_thekey  items.thekey%type:=null;
begin
     dbms_output.put_line('thekey    thereportstring');
     for rep_rec in rep_dta loop
           if prev_thekey is null then
                 dbms_output.put(rep_rec.thekey||'
'||rep_rec.status||rep_rec.value);
           elsif prev_thekey != rep_rec.thekey then
                 dbms_output.put_line('');
                 dbms_output.put(rep_rec.thekey||'
'||rep_rec.status||rep_rec.value);
           else
                 dbms_output.put(rep_rec.status||rep_rec.value);
           end if;
           prev_thekey := rep_rec.thekey;
     end loop;
     dbms_output.put_line('');

end;
/

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Wed Jul 12 2000 - 00:00:00 CDT