Forum Stats

  • 3,734,034 Users
  • 2,246,862 Discussions
  • 7,857,003 Comments

Discussions

Query Help Please

Girish Sharma
Girish Sharma Member Posts: 4,980 Bronze Crown
edited June 2008 in SQL & PL/SQL
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

Comments

  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    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
    Girish Sharma Member Posts: 4,980 Bronze Crown
    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
    MichaelS Member Posts: 8,424 Bronze Crown
    edited June 2008
    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
    Girish Sharma Member Posts: 4,980 Bronze Crown
    So many thanks...!
    Its all due to your hardworking and experience.

    Kind Regards
    Girish Sharma
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    edited June 2008
    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
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    edited June 2008
    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
    Girish Sharma Member Posts: 4,980 Bronze Crown
    edited June 2008
    Sir,
    So many thanks...!

    Kind Regards
    Girish

    Girish Sharma
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    edited June 2008
    ok, you see the modifications.

    Message was edited by:
    N. Gasparotto
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited June 2008
    612816
    This thread deals similar question.
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    edited June 2008
    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
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    @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
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    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.
This discussion has been closed.