Query Help Please

Girish Sharma

Hello,

Using : Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

        Windows XP Service Pack 1

Please help me to write a select query as below:

create table student(eno varchar2(10),rno varchar2(5),r0 number(5),r1 number(5),r2 number(5),r3 number(5));
insert into student values ('9352','A0000',0,0,0,0);
insert into student values ('15401','A0006',0,0,0,0);
insert into student values ('9478','A0000',0,0,0,0);
insert into student values ('6712','A0000',0,0,0,0);
insert into student values ('12410','A0006',0,0,0,0);
insert into student values ('9477','A0001',0,0,0,0);
commit;

Required output:

ENO        RNO           R0         R1         R2         R3

---------- ----- ---------- ---------- ---------- ----------

9352       A0000       6712       9478          0          0<--column output Should be asc order for column R0 to R3

15401      A0006      12410          0          0          0

9478       A0000       6712       9352          0          0

6712       A0000       9352       9478          0          0

12410      A0006      15401          0          0          0

9477       A0001          0          0          0          0

There will not more than 5 rows for a RNO. I am not getting any clue to get the output; so till now i have'nt write anything to get output.

Please guide me.

Thanks & Regards
Girish Sharma

MichaelS

One way could be

SQL>  select eno, rno, 
      nvl((select xmlagg(xmlelement(x, eno) order by eno).extract('X[1]/text()').getnumberval() from student s2 where s2.rno=s.rno and s2.eno != s.eno),0) r0,
      nvl((select xmlagg(xmlelement(x, eno) order by eno).extract('X[2]/text()').getnumberval() from student s2 where s2.rno=s.rno and s2.eno != s.eno),0) r1,
      nvl((select xmlagg(xmlelement(x, eno) order by eno).extract('X[3]/text()').getnumberval() from student s2 where s2.rno=s.rno and s2.eno != s.eno),0) r2,
      nvl((select xmlagg(xmlelement(x, eno) order by eno).extract('X[4]/text()').getnumberval() from student s2 where s2.rno=s.rno and s2.eno != s.eno),0) r3
  from student s

ENO        RNO           R0         R1         R2         R3
---------- ----- ---------- ---------- ---------- ----------
9352       A0000       6712       9478          0          0
15401      A0006      12410          0          0          0
9478       A0000       6712       9352          0          0
6712       A0000       9352       9478          0          0
12410      A0006      15401          0          0          0
9477       A0001          0          0          0          0

6 rows selected.

Girish Sharma

Thanks Sir.

SQL> select eno, rno,

  2        nvl((select xmlagg(xmlelement(x, eno) order by eno).extract('X[1]/text()').getnumberval() from student s2 where s2.rno=s.rno and s2.eno != s.eno),0) r0,

  3        nvl((select xmlagg(xmlelement(x, eno) order by eno).extract('X[2]/text()').getnumberval() from student s2 where s2.rno=s.rno and s2.eno != s.eno),0) r1,

  4        nvl((select xmlagg(xmlelement(x, eno) order by eno).extract('X[3]/text()').getnumberval() from student s2 where s2.rno=s.rno and s2.eno != s.eno),0) r2,

  5        nvl((select xmlagg(xmlelement(x, eno) order by eno).extract('X[4]/text()').getnumberval() from student s2 where s2.rno=s.rno and s2.eno != s.eno),0) r3

  6    from student s

  7  /

      nvl((select xmlagg(xmlelement(x, eno) order by eno).extract('X[1]/text()').getnumberval() from student s2 where s2.rno=s.rno and s2.eno != s.eno),0) r0,

                                            *

ERROR at line 2:

ORA-00907: missing right parenthesis

Please guide me; since i am not aware of XML query.

Thanks & Kind Regards
Girish Sharma

MichaelS

must be due to 9.2.0.1.0 not supporting order by in xmlagg.

Another modified version which avoids order by in xmlagg is

SQL>  select t2.column_value.extract('X/text()').getnumberval() eno, 
       rno,
       nvl(t.x.extract('X[text() != ' || t2.column_value.extract('X/text()') || '][1]/text()').getnumberval(),0) r0,
       nvl(t.x.extract('X[text() != ' || t2.column_value.extract('X/text()') || '][2]/text()').getnumberval(),0) r1,
       nvl(t.x.extract('X[text() != ' || t2.column_value.extract('X/text()') || '][3]/text()').getnumberval(),0) r2,
       nvl(t.x.extract('X[text() != ' || t2.column_value.extract('X/text()') || '][4]/text()').getnumberval(),0) r3                   
  from (select rno, xmlagg(xmlelement(x,eno)) x 
          from (select rno, eno from student order by eno)
         group by rno) t, 
       table(xmlsequence(t.x.extract('X'))) t2

       ENO RNO           R0         R1         R2         R3
---------- ----- ---------- ---------- ---------- ----------
      6712 A0000       9352       9478          0          0
      9352 A0000       6712       9478          0          0
      9478 A0000       6712       9352          0          0
      9477 A0001          0          0          0          0
     12410 A0006      15401          0          0          0
     15401 A0006      12410          0          0          0

6 rows selected.

Message was edited by:
michaels

Girish Sharma

So many thanks...!
Its all due to your hardworking and experience.

Kind Regards
Girish Sharma

Nicolas Gasparotto

Just an other way, maybe more understandable :

SQL> select a_eno eno,

  2         rno,

  3         nvl(max(decode(rn,1,b_eno)),0) as r0,

  4         nvl(max(decode(rn,2,b_eno)),0) as r1,

  5         nvl(max(decode(rn,3,b_eno)),0) as r2,

  6         nvl(max(decode(rn,4,b_eno)),0) as r3

  7  from   (select a.eno a_eno, a.rno, b.eno b_eno, dense_rank() over (partition by a.rno,a.eno order by b.eno) rn

  8          from   student a, student b

  9          where  a.rno=b.rno

 10          and    a.eno!=b.eno)

 11  group by a_eno,rno

 12  order by rno,eno;


ENO        RNO   R0         R1         R2         R3

---------- ----- ---------- ---------- ---------- ----------

6712       A0000 9352       9478       0          0

9352       A0000 6712       9478       0          0

9478       A0000 6712       9352       0          0

12410      A0006 15401      0          0          0

15401      A0006 12410      0          0          0


SQL>

Nicolas.

Doesn't work, please see next post.
Message was edited by:
N. Gasparotto

Nicolas Gasparotto

Small correction when a eno-rno is alone.
Now that should work :

SQL> select a_eno eno,

  2         rno,

  3         nvl(max(decode(rn,1,b_eno)),0) as r0,

  4         nvl(max(decode(rn,2,b_eno)),0) as r1,

  5         nvl(max(decode(rn,3,b_eno)),0) as r2,

  6         nvl(max(decode(rn,4,b_eno)),0) as r3

  7  from   (select a.eno a_eno, a.rno, b.eno b_eno, decode(a.eno,b.eno,null,row_number() over (partition by a.rno,a.eno order by decode(a.eno,b.eno,null,b.eno))) rn

  8          from   student a, student b

  9          where  a.rno=b.rno)

 10  group by a_eno,rno

 11  order by rno,eno;


ENO        RNO   R0         R1         R2         R3

---------- ----- ---------- ---------- ---------- ----------

6712       A0000 9352       9478       0          0

9352       A0000 6712       9478       0          0

9478       A0000 6712       9352       0          0

9477       A0001 0          0          0          0

12410      A0006 15401      0          0          0

15401      A0006 12410      0          0          0


6 rows selected.

Nicolas.

Message was edited by:
N. Gasparotto

Girish Sharma

Sir,
So many thanks...!

Kind Regards
Girish

Girish Sharma

Nicolas Gasparotto

ok, you see the modifications.

Message was edited by:
N. Gasparotto

Aketi Jyuuzou

612816
This thread deals similar question.

MichaelS

Just an other way, maybe more understandable :

Hopefully following is more understandable and also avoids a self join:

select eno,
       rno, 
       decode (r,1,rn5,2,rn4,3,rn3,4,rn2,5,rn1) r0,
       decode (r,1,rn6,2,rn5,3,rn4,4,rn3,5,rn2) r1,
       decode (r,1,rn7,2,rn6,3,rn5,4,rn4,5,rn3) r2,
       decode (r,1,rn8,2,rn7,3,rn6,4,rn5,5,rn4) r3
  from (select eno, rno,
               row_number()  over (partition by rno order by eno) r,
               lag(eno,4,0)   over (partition by rno order by eno) rn1,
               lag(eno,3,0)   over (partition by rno order by eno) rn2,
               lag(eno,2,0)   over (partition by rno order by eno) rn3,
               lag(eno,1,0)   over (partition by rno order by eno) rn4,
               lead(eno,1,0)  over (partition by rno order by eno) rn5,
               lead(eno,2,0)  over (partition by rno order by eno) rn6,
               lead(eno,3,0)  over (partition by rno order by eno) rn7,
               lead(eno,4,0)  over (partition by rno order by eno) rn8
          from student) s

ENO        RNO   R0         R1         R2         R3        
---------- ----- ---------- ---------- ---------- ----------
6712       A0000 9352       9478       0          0         
9352       A0000 6712       9478       0          0         
9478       A0000 6712       9352       0          0         
9477       A0001 0          0          0          0         
12410      A0006 15401      0          0          0         
15401      A0006 12410      0          0          0         

6 rows selected.

french, currently in the Netherlands

in either case not much luck in uefa cup since yesterday

made some minor fixes.

Message was edited by:
michaels

Aketi Jyuuzou

@michaels
your solution is excellent.

My complex solution ;-)

select a.eno,a.rno,

case when b.R0 > 0 then Lead(a.eno,abs(b.R0),0) over(partition by a.rno order by a.eno)

     else Lag(a.eno,abs(b.R0),0) over(partition by a.rno order by a.eno) end as R0,

case when b.R1 > 0 then Lead(a.eno,abs(b.R1),0) over(partition by a.rno order by a.eno)

     else Lag(a.eno,abs(b.R1),0) over(partition by a.rno order by a.eno) end as R1,

case when b.R2 > 0 then Lead(a.eno,abs(b.R2),0) over(partition by a.rno order by a.eno)

     else Lag(a.eno,abs(b.R2),0) over(partition by a.rno order by a.eno) end as R2,

case when b.R3 > 0 then Lead(a.eno,abs(b.R3),0) over(partition by a.rno order by a.eno)

     else Lag(a.eno,abs(b.R3),0) over(partition by a.rno order by a.eno) end as R3

  from (select eno,rno,r0,r1,r2,r3,

        Row_Number() over(partition by rno order by eno) as JoinKey

          from student) a Join

       (select 1 as JoinKey,1 as R0,2 as R1,3 as R2,4 as R3 from dual union all

        select 2,-1, 1, 2, 3 from dual union all

        select 3,-2,-1, 1, 2 from dual union all

        select 4,-3,-2,-1, 1 from dual union all

        select 5,-4,-3,-2,-1 from dual) b

 using(JoinKey)

order by a.eno,a.rno;

  ENO  RNO       R0    R1  R2  R3

-----  -----  -----  ----  --  --

 6712  A0000   9352  9478   0   0

 9352  A0000   6712  9478   0   0

 9477  A0001      0     0   0   0

 9478  A0000   6712  9352   0   0

12410  A0006  15401     0   0   0

15401  A0006  12410     0   0   0

Nicolas Gasparotto

Michaels,

Just an other way, maybe more understandable :

Hopefully following is more understandable and also avoids a self join:

Nice shot !

french, currently in the Netherlands

in either case not much luck in uefa cup since yesterday

...unlike Germans... so far.

Nicolas.