Re: help with a query

From: Jorge Castro <joacat_at_crosswinds.net>
Date: Thu, 24 Aug 2000 12:28:07 -0400
Message-ID: <8o3ibh$9s4g2$1@ID-47021.news.cis.dfn.de>

Thanks!!! It works fine, when I run the query the result was:

     TYPE1 VALUE1 TYPE2 VALU2 TYPE0 VALUE0 ---------- ---------- ---------- ---------- ---------- ----------

         0       5000         14          0         19          0
        11        500         18         45         24          0
         9         25         17         45         20          0
         8        800         16         20         21          0
        10          0         15        456         23        100
         7        200          6       85,5         22          0
         3         15          5    1068,75
         2       2000          4     676,85
         1         10
        13          0
        12          0

so, I modify your query this way:

select

  t1.id as type1, t1.value as value1,
  t2.id as type2, t2.value as value2,
  t0.id as type0, t0.value as value0

from
(select rownum as ord1, id, value from
  (select id, value from table where type=1   order by id)) t1,
(select rownum as ord2, id, value from
  (select id, value from table where type=2   order by id)) t2,
(select rownum as ord3, id, value from
  (select id, value from table where type=0   order by id)) t0
where
  ord1 = ord2(+) and ord1 = ord3(+);

and the result was what I want:

     TYPE1 VALUE1 TYPE2 VALUE2 TYPE0 VALUE0 ---------- ---------- ---------- ---------- ---------- ----------

         0       5000          4     676,85         19          0
         1         10          5    1068,75         20          0
         2       2000          6       85,5         21          0
         3         15         14          0         22          0
         7        200         15        456         23        100
         8        800         16         20         24          0
         9         25         17         45
        10          0         18         45
        11        500
        12          0
        13          0

Thanks a lot again.

"f morel" <florian.morel_at_actiware.fr> escribió en el mensaje news:8o3gd3$q45$1_at_wanadoo.fr...
> It seems to be complex.... could you try the following query? Please tell
 me
> if it works!!
>
>
> select t1.id as type1, t1.value as value1,
> t2.id as type2, t2.value as valu2,
> t0.id as type0, t0.value as value0
> from
> (select rownum as ord1, id, value from table
> where type=1
> order by id) t1,
> (select rownum as ord2, id, value from table
> where type=2
> order by id) t2,
> (select rownum as ord3, id, value from table
> where type=0
> order by id) t0
> where ord1 = ord2(+)
> and ord1 = ord3(+)
Received on Thu Aug 24 2000 - 11:28:07 CDT