Home » SQL & PL/SQL » SQL & PL/SQL » Select Query Help (10.2.0.1)
Select Query Help [message #446191] |
Sat, 06 March 2010 02:12  |
GirishSharma
Messages: 22 Registered: March 2010
|
Junior Member |
|
|
Hello,
I am using oracle 10.2.0.1 on windows xp. Please help me to write select query to get required outputs:
create table xx(cen varchar2(5),secrol varchar2(7));
create table yy(cen varchar2(5),srrol varchar2(7));
insert into xx values ('01004','0001110');
insert into xx values ('01004','0001111');
insert into xx values ('01004','0001112');
insert into xx values ('01004','0001113');
insert into xx values ('01004','0001114');
insert into yy values ('01004','0361124');
insert into yy values ('01004','0361125');
insert into yy values ('01004','0361126');
insert into xx values ('01006','0001140');
insert into xx values ('01006','0001141');
insert into xx values ('01006','0001142');
insert into yy values ('01010','0361400');
insert into yy values ('01010','0361401');
insert into yy values ('01010','0361402');
insert into yy values ('01015','0361608');
insert into yy values ('01015','0361609');
insert into yy values ('01015','0361610');
insert into yy values ('01015','0361611');
insert into xx values ('01015','0001160');
insert into xx values ('01015','0001161');
commit;
1.required output:
cen secrol srrol
01004 0001110 0361124
01004 0001111 0361125
01004 0001112 0361126
01004 0001113
01004 0001114
01006 0001140
01006 0001141
01006 0001142
01010 0361400
01010 0361401
01010 0361402
01015 0001160 0361608
01015 0001161 0361609
01015 0361610
01015 0361611
2.required output: (more complex 3 roll numbers in a column row.)
cen secrols srrols sectot srtot
01004 0001110,0001111,0001112 0361124,0361125,0361126 5 3
0001113,000114
01006 0001140,0001141,0001142 3 0
01010 0361400,0361401,0361402 0 3
01015 0001160,0001161 0361608,0361609,0361610, 2 4
0361611
Means, there two tables xx (for secondary exam students) and yy for (sr.secondary exam students) having 2 columns cen in both and roll number columns.
I do'nt know i am using right code tag, because its my first post in the forum.
Thank you.
Regards
Girish Sharma
|
|
|
Re: Select Query Help [message #446193 is a reply to message #446191] |
Sat, 06 March 2010 02:50   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Please explain with words the line of your outputs.
Where and how does come
Where and how does come
Where and how does come
Where and how does come
Where and how does come
It would be easier to read if you don't mix insert from xx and from yy or at least separate the "groups" by a blank line.
This said thanks for the test case.
Regards
Michel
[Updated on: Sat, 06 March 2010 02:50] Report message to a moderator
|
|
|
Re: Select Query Help [message #446194 is a reply to message #446191] |
Sat, 06 March 2010 02:56   |
GirishSharma
Messages: 22 Registered: March 2010
|
Junior Member |
|
|
Thank you Michel for your reply.
01004 (cen column from any table xx/yy)
0001110 (is the roll number of cen=01004 from xx table)
0361124 (is the roll number of cen=01004 from yy table)
0001113 (is the roll number of cen=01004 from xx table and there is no roll number in yy for cen=01004; so blank); likewise
0361400 (is the roll number of cen=01010 from yy table and there is no roll number in xx for cen=01010; so blank).
Please let me know, if you wish to clearify more.
Regards
Girish Sharma
|
|
|
Re: Select Query Help [message #446197 is a reply to message #446194] |
Sat, 06 March 2010 03:04   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
SQL> select * from xx order by 1,2;
CEN SECROL
----- -------
01004 0001110
01004 0001111
01004 0001112
01004 0001113
01004 0001114
01006 0001140
01006 0001141
01006 0001142
01015 0001160
01015 0001161
10 rows selected.
SQL> select * from yy order by 1,2;
CEN SRROL
----- -------
01004 0361124
01004 0361125
01004 0361126
01010 0361400
01010 0361401
01010 0361402
01015 0361608
01015 0361609
01015 0361610
01015 0361611
10 rows selected.
SQL> with
2 xx1 as (
3 select cen, secrol,
4 row_number() over(partition by cen order by secrol) rn
5 from xx
6 ),
7 yy1 as (
8 select cen, srrol,
9 row_number() over(partition by cen order by srrol) rn
10 from yy
11 )
12 select nvl(x.cen,y.cen) cen, x.secrol, y.srrol
13 from xx1 x full outer join yy1 y
14 on (x.cen = y.cen and x.rn = y.rn)
15 order by 1, nvl(x.rn,y.rn)
16 /
CEN SECROL SRROL
----- ------- -------
01004 0001110 0361124
01004 0001111 0361125
01004 0001112 0361126
01004 0001113
01004 0001114
01006 0001140
01006 0001141
01006 0001142
01010 0361400
01010 0361401
01010 0361402
01015 0001160 0361608
01015 0001161 0361609
01015 0361610
01015 0361611
15 rows selected.
SQL> col secrols format a24
SQL> col srrols format a24
SQL> with
2 xx1 as (
3 select cen, secrol,
4 row_number() over(partition by cen order by secrol) rn
5 from xx
6 ),
7 yy1 as (
8 select cen, srrol,
9 row_number() over(partition by cen order by srrol) rn
10 from yy
11 ),
12 data as (
13 select nvl(x.cen,y.cen) cen, x.secrol, y.srrol,
14 count(x.secrol) over (partition by nvl(x.cen,y.cen)) sectot,
15 count(y.srrol) over (partition by nvl(x.cen,y.cen)) srtot,
16 row_number() over (partition by nvl(x.cen,y.cen) order by nvl(x.rn,y.rn)) rn,
17 count(*) over (partition by nvl(x.cen,y.cen)) cnt
18 from xx1 x full outer join yy1 y
19 on (x.cen = y.cen and x.rn = y.rn)
20 )
21 select cen,
22 trim(',' from sys_connect_by_path(secrol,',')) secrols,
23 trim(',' from sys_connect_by_path(srrol,',')) srrols,
24 sectot,
25 srtot
26 from data
27 where rn = cnt
28 connect by prior cen = cen and prior rn = rn-1
29 start with rn = 1
30 order by cen
31 /
CEN SECROLS SRROLS SECTOT SRTOT
----- ------------------------ ------------------------ ---------- ----------
01004 0001110,0001111,0001112, 0361124,0361125,0361126 5 3
0001113,0001114
01006 0001140,0001141,0001142 3 0
01010 0361400,0361401,0361402 0 3
01015 0001160,0001161 0361608,0361609,0361610, 2 4
0361611
4 rows selected.
Regards
Michel
|
|
|
Re: Select Query Help [message #446200 is a reply to message #446191] |
Sat, 06 March 2010 03:14   |
GirishSharma
Messages: 22 Registered: March 2010
|
Junior Member |
|
|
Thank you Michel for your very much worthy reply.
First query run fine without any error as per my required output.
But second query is having problem:
SQL> col secrols format a24
SQL> col srrols format a24
SQL> with
2 xx1 as (
3 select cen, secrol,
4 row_number() over(partition by cen order by secrol) rn
5 from xx
6 ),
7 yy1 as (
8 select cen, srrol,
9 row_number() over(partition by cen order by srrol) rn
10 from yy
11 ),
12 data as (
13 select nvl(x.cen,y.cen) cen, x.secrol, y.srrol,
14 count(x.secrol) over (partition by nvl(x.cen,y.cen)) sectot,
15 count(y.srrol) over (partition by nvl(x.cen,y.cen)) srtot,
16 row_number() over (partition by nvl(x.cen,y.cen) order by nvl(x.rn,y.rn)) rn,
17 count(*) over (partition by nvl(x.cen,y.cen)) cnt
18 from xx1 x full outer join yy1 y
19 on (x.cen = y.cen and x.rn = y.rn)
20 )
21 select cen,
22 trim(',' from sys_connect_by_path(secrol,',')) secrols,
23 trim(',' from sys_connect_by_path(srrol,',')) srrols,
24 sectot,
25 srtot
26 from data
27 where rn = cnt
28 connect by prior cen = cen and prior rn = rn-1
29 start with rn = 1
30 order by cen
31 /
with
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long
It may be due to so many rows in a particular centre; that why it is not able to concatinate the complete roll numbers for that particular centre. Please see, how to overcome the issue. If it overcomes by adding more roll numbers (3 to 5/6) then also no problem.
Best Regards
Girish Sharma
|
|
|
Re: Select Query Help [message #446203 is a reply to message #446200] |
Sat, 06 March 2010 03:46   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Yes you are limited to 4000 bytes.
You can split into groups.
For instance (in this example I group by 3 but you can choose any value you want):
SQL> def nb_in_group=3
SQL> with
2 xx1 as (
3 select cen, secrol,
4 row_number() over(partition by cen order by secrol)-1 rn
5 from xx
6 ),
7 yy1 as (
8 select cen, srrol,
9 row_number() over(partition by cen order by srrol)-1 rn
10 from yy
11 ),
12 xx2 as (
13 select cen, secrol, trunc(rn/&nb_in_group) grp, mod(rn,&nb_in_group) rn
14 from xx1
15 ),
16 yy2 as (
17 select cen, srrol, trunc(rn/&nb_in_group) grp, mod(rn,&nb_in_group) rn
18 from yy1
19 ),
20 data as (
21 select nvl(x.cen,y.cen) cen, x.secrol, y.srrol, nvl(x.grp,y.grp) grp,
22 count(x.secrol) over (partition by nvl(x.cen,y.cen), nvl(x.grp,y.grp)) sectot,
23 count(y.srrol) over (partition by nvl(x.cen,y.cen), nvl(x.grp,y.grp)) srtot,
24 row_number() over (partition by nvl(x.cen,y.cen), nvl(x.grp,y.grp) order by nvl(x.rn,y.rn)) rn,
25 count(*) over (partition by nvl(x.cen,y.cen), nvl(x.grp,y.grp)) cnt
26 from xx2 x full outer join yy2 y
27 on (x.cen = y.cen and x.grp = y.grp and x.rn = y.rn)
28 )
29 select cen,
30 trim(',' from sys_connect_by_path(secrol,',')) secrols,
31 trim(',' from sys_connect_by_path(srrol,',')) srrols,
32 sectot, srtot
33 from data
34 where rn = cnt
35 connect by prior cen = cen and prior grp = grp and prior rn = rn-1
36 start with rn = 1
37 order by cen, grp
38 /
CEN SECROLS SRROLS SECTOT SRTOT
----- ------------------------ ------------------------ ---------- ----------
01004 0001110,0001111,0001112 0361124,0361125,0361126 3 3
01004 0001113,0001114 2 0
01006 0001140,0001141,0001142 3 0
01010 0361400,0361401,0361402 0 3
01015 0001160,0001161 0361608,0361609,0361610 2 3
01015 0361611 0 1
6 rows selected.
I gave the count by line (i.e. group), now it depends on what you prefer. It could the total per cen on each row or an additional line giving the total for each cen.
Regards
Michel
|
|
|
Re: Select Query Help [message #446205 is a reply to message #446191] |
Sat, 06 March 2010 04:20   |
GirishSharma
Messages: 22 Registered: March 2010
|
Junior Member |
|
|
Michel, Thank you very much.
I am just hypnotized with the query and power of oracle...!!!
Once again heartly thanks with God bless you.
Kind Regards
Girish Sharma
|
|
|
Re: Select Query Help [message #446219 is a reply to message #446191] |
Sat, 06 March 2010 06:47   |
GirishSharma
Messages: 22 Registered: March 2010
|
Junior Member |
|
|
Michel,
def nb_in_group=6
break on cen skip 1
col secrols for a54
col srrols for a48
set line 132
set head off
set pagesize 65
BTITLE LEFT 'PAGE:' FORMAT 999 SQL.PNO ''
TTITLE LEFT 'MY COMPANY NAME' SKIP 1 -
COL 1 'WHAT IS THIS REPORT' SKIP 1 -
COL 1 '===================================================================================================================================' SKIP 1 -
COL 1 'CENT <---------SECONDARY ROLL NUMBERS--------------> <-------------SR.SEC. ROLL NUMBERS------------> SECTOT SRTOT' SKIP 1 -
COL 1 '==================================================================================================================================='
spool file1.txt
...Here your query run with 100% success...
And i got the beautiful output.
I am very sorry that i am again disturbing you but since client is "NOW" saying that we missed to tell you to add the cenname in the report too.
Here is third table (cen) :
create table cen(cen varchar2(5),name varchar2(60));
insert into cen values ('01004','CENT1');
insert into cen values ('01006','CENT2');
insert into cen values ('01010','CENT3');
insert into cen values ('01015','CENT4');
COMMIT;
Now required output should contain the respective name (column of cen table). Like :
01004 CENT1 0001717,0001718,0001719,0001720 1100461,1100462,1100463,1100464 4 4
Means a new column (cen.name) is to be added. Since even i still trying to understand the query, its not possible for me to edit the query for add the column. One more thing; since there is limited space on the page and this cen.name is having width of 60 characters; i wish that they should be wrap in within 40 characters.
I shall be once again highly thankful to you and i am really very sorry that now bit requirement (new column addition) changed.
Kind Regards
Girish Sharma
|
|
|
Re: Select Query Help [message #446222 is a reply to message #446219] |
Sat, 06 March 2010 09:11   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
It it the beauty of WITH clause that you can split the query in steps and add some there:
SQL> col name format a6
SQL> with
2 xx1 as (
3 select cen, secrol,
4 row_number() over(partition by cen order by secrol)-1 rn
5 from xx
6 ),
7 yy1 as (
8 select cen, srrol,
9 row_number() over(partition by cen order by srrol)-1 rn
10 from yy
11 ),
12 xx2 as (
13 select cen, secrol, trunc(rn/&nb_in_group) grp, mod(rn,&nb_in_group) rn
14 from xx1
15 ),
16 yy2 as (
17 select cen, srrol, trunc(rn/&nb_in_group) grp, mod(rn,&nb_in_group) rn
18 from yy1
19 ),
20 data as (
21 select nvl(x.cen,y.cen) cen, x.secrol, y.srrol, nvl(x.grp,y.grp) grp,
22 count(x.secrol) over (partition by nvl(x.cen,y.cen), nvl(x.grp,y.grp)) sectot,
23 count(y.srrol) over (partition by nvl(x.cen,y.cen), nvl(x.grp,y.grp)) srtot,
24 row_number() over (partition by nvl(x.cen,y.cen), nvl(x.grp,y.grp) order by nvl(x.rn,y.rn)) rn,
25 count(*) over (partition by nvl(x.cen,y.cen), nvl(x.grp,y.grp)) cnt
26 from xx2 x full outer join yy2 y
27 on (x.cen = y.cen and x.grp = y.grp and x.rn = y.rn)
28 ),
29 previous_one as (
30 select cen, grp,
31 trim(',' from sys_connect_by_path(secrol,',')) secrols,
32 trim(',' from sys_connect_by_path(srrol,',')) srrols,
33 sectot, srtot
34 from data
35 where rn = cnt
36 connect by prior cen = cen and prior grp = grp and prior rn = rn-1
37 start with rn = 1
38 )
39 select p.cen, c.name, p.secrols, p.srrols, p.sectot, p.srtot
40 from previous_one p, cen c
41 where c.cen = p.cen
42 order by p.cen, p.grp
43 /
CEN NAME SECROLS SRROLS SECTOT SRTOT
----- ------ ------------------------ ------------------------ ---------- ----------
01004 CENT1 0001110,0001111,0001112 0361124,0361125,0361126 3 3
01004 CENT1 0001113,0001114 2 0
01006 CENT2 0001140,0001141,0001142 3 0
01010 CENT3 0361400,0361401,0361402 0 3
01015 CENT4 0001160,0001161 0361608,0361609,0361610 2 3
01015 CENT4 0361611 0 1
6 rows selected.
Regards
Michel
[Updated on: Sat, 06 March 2010 10:46] Report message to a moderator
|
|
|
|
Goto Forum:
Current Time: Tue Feb 11 10:25:20 CST 2025
|