Re: Query Help

Hi Evan,

This is exactly what I was after. My data was exactly as you described:

 ROWIDPK DESCRIPTIVENAME COL2 COL3 COL4 COL5 COL6

• --------------- ---- ---- ---- ---- ---- 1 Cows 1 NULL 5 3 5 2 Dogs 2 5 NULL 7 NULL 3 Cats 3 6 5 4 3 4 Bird 6 7 4 NULL 4 5 Fish 6 NULL NULL NULL 1 6 Man 6 7 NULL NULL 1 7 Owls NULL 7 NULL NULL 1

I tried your query. But it's giving me this:

 ROWIDPK DESCRIPTIVENAME COL2 COL3 COL4 COL5 COL6

• --------------- ---- ---- ---- ---- ---- 1 Cows Cows - - - - 2 Dogs Dogs - - - - 3 Cats Cats - - - Cats 4 Bird - - Bird - Bird 5 Fish - - - - - 6 Man Man - - - - 7 Owls - Owls - - -

I was hoping it would return this:

 ROWIDPK DESCRIPTIVENAME COL2 COL3 COL4 COL5 COL6

• --------------- ---- ---- ---- ---- ---- 1 Cows Cows - Fish Cats Fish 2 Dogs Dogs Fish - Owls - 3 Cats Cats Man Fish Bird Cats 4 Bird Man Owls Bird - Bird 5 Fish Man - - - Cows 6 Man Man Owls - - Cows 7 Owls - Owls - - Cows

Evan <silverback_at_photobooks.com> wrote in message news:<pegkg01l9e875k7ld61qsvk4n5nc4umg2g_at_4ax.com>...
> Is this homework?
>
> You need to post some rows and what you want for results, but...
>
> ROWIDPK DESCRIPTIVENAME COL2 COL3 COL4 COL5 COL6
> ------- --------------- ---- ---- ---- ---- ----
> 1 Cows 0 0 1 3 5
> 2 Dogs 2 5 6 7 0
> 3 Cats 7 6 5 4 3
> 4 NotThere 6 7 8 9 1
>
> select rowidPK, descriptiveName DescName,
> decode(rowidPK,col2, descriptiveName, ' - ') col2,
> decode(rowidPK,col3, descriptiveName, ' - ') col3,
> decode(rowidPK,col4, descriptiveName, ' - ') col4,
> decode(rowidPK,col5, descriptiveName, ' - ') col5,
> decode(rowidPK,col6, descriptiveName, ' - ') col6
> from mytable
>
> ROWIDPK DESCNAME COL2 COL3 COL4 COL5 COL6
> ------- ---------- ----- ----- ----- ----- -----
> 1 Cows - - Cows - -
> 2 Dogs Dogs - - - -
> 3 Cats - - - - Cats
> 4 NotThere - - - - -
> or
>
> select descriptiveName DescName
> from mytable
> where rowidPK in (col2,col3, col4, col5, col6)
>
> DESCNAME
> ----------
> Cows
> Dogs
> Cats
>
> On 29 Jul 2004 10:34:32 -0700, rnurse_at_cudbytech.net (Robert Nurse) wrote:
>
> >Hi All,
> >
> >I've got a report to do and I need a little help with the query.
> >Here's the table structure
> >
> >rowID PK
> >descriptiveName
> >Col2 FK
> >Col3
> >Col4
> >Col5
> >Col6
> >
> >Col3 through Col6 contain values taken from rowID. Just as a side
> >note, this table is not normalized. Correct? Anyway, when I query
> >the table instead of displaying the data in Col3 through Col6, I'd
> >like to display the corresponding descriptiveName for those values.
> >Could someone give me a hint at a PL/SQL solution for this? I'm new
> >to PL/SQL.
Received on Fri Jul 30 2004 - 14:56:10 CDT