Re: Non-normalized Join Problem

John L Dunn <misioror_at_GAS.UUG.Arizona.EDU> wrote:

>What it doesn't solve is how to display a record showing all three
>diagnosis on the same line, i.e., given the following,
>
>> > Invoice Table Diagnosis Table
>> > ----------------------------- -------------------------------
>> > Inv# Dx1 Dx2 Dx3 Record# Diagnosis
>> > 123 1 2 1 Influenza
>> > 234 2 3 4 2 Bad Karma
>> > 345 3 1 3 Hypertension
>> > etc etc
>> >
>
>display:
>
>Inv# Dx1 Dx2 Dx3
>123 Influenza Bad Karma
>234 Bad Karma Hypertension Diagnosis #4
>345 Hypertension Influenza
>
>THIS is the real problem.
>

Effectively, the solution that you want requires creating another table where the entries under the Dxn's are the string names substituted for the various codes.

The Diagnosis table is then used as an array:

Diag[1] = "Influenza"
Diag[2] = "Bad Karma"
..

in a pl/sql procedure that translates from the original Invoice (numeric) table into the new Invoice (string) table.

Here's a kludge version using pl*sql's decode:

--

create table newinvoice (
Invno number(--),
Dx1   number(--),
..
Dxx1  varchar2(40), -- A string column for every corresp. numeric col
..
);

insert into newinvoice (Invno,Dx1,..,Dxn)
    select Invno,Dx1,...,Dxn from invoice;

-- For every numeric col in newinvoice. beginning with Dx1:

update newinvoice 
    set Dxx1 = decode(Dx1,1,"Influenza",2,"Bad Karma",...),
    set Dxx2 = decode ....
;


--
Its inelegant but it gives you the flavor for what the pl*sql procedure 
has to do.


ayn