| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Query Help
SQL> ed
Wrote file afiedt.buf
1 select t1.rowidpk, descriptivename,c2d,c3d,c4d,c5d,c6d 2 from mytable t1,
3 (select rowidpk,descriptivename c2d from mytable) t2, 4 (select rowidpk,descriptivename c3d from mytable) t3, 5 (select rowidpk,descriptivename c4d from mytable) t4, 6 (select rowidpk,descriptivename c5d from mytable) t5, 7 (select rowidpk,descriptivename c6d from mytable) t6 8 where t1.col2 = t2.rowidpk(+) and 9 t1.col3 = t3.rowidpk(+) and 10 t1.col4 = t4.rowidpk(+) and 11 t1.col5 = t5.rowidpk(+) and 12* t1.col6 = t6.rowidpk(+)
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
7 rows selected.
"Robert Nurse" <rnurse_at_cudbytech.net> wrote in message
news:a967f5b9.0407301156.b0437f6_at_posting.google.com...
> 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 - 22:35:38 CDT
 |
 |