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 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please explain with words the line of your outputs.
Where and how does come
01004   0001110     0361124

Where and how does come
01004   0001111     0361125

Where and how does come
01004   0001112     0361126

Where and how does come
01004   0001113

Where and how does come
01010               0361400

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: Select Query Help [message #446328 is a reply to message #446191] Sun, 07 March 2010 22:24 Go to previous message
GirishSharma
Messages: 22
Registered: March 2010
Junior Member
Michel,
Thank you very much.

Regards
Girish Sharma
Previous Topic: Joins
Next Topic: find total sales for the whole last week
Goto Forum:
  


Current Time: Sun Dec 04 10:31:07 CST 2016

Total time taken to generate the page: 0.13397 seconds