Home » SQL & PL/SQL » SQL & PL/SQL » Order by clause doesn't work as wanted (Oracle 9i)
Order by clause doesn't work as wanted [message #291202] Thu, 03 January 2008 06:34 Go to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
Hi,
I am trying to modify a script file(report) -The output is in csv format..Everything works fine except the order by clause.

When I run the select statement in TOAD(select statement by the cursor c2 in the script) it works correctly and the sorting by columns is in the order I want.But when that comes into report, this does not happen.
The order I want in the report is,columns 3,4 and 5 in the select statement by cursor c2.(post town,delivery office,route)
In the report, it is not coming as sorted by any column.
I have tried to re arrange in several ways but it never works.
Can somebody pls help me?
 set serveroutput on size 1000000
set pages 10000
set lines 1000
set arraysize 1
set trimspool on
set heading on

PROMPT Checking for Multiple Building Group Names


declare
  cursor c1 is
  select tow.name town,g.name grp
  from t_building_groups g,
       t_thoroughfares t,
       t_towns tow
  where g.thorfare_id = t.thorfare_id
  and   t.town_id     = tow.town_id
  group by tow.name,g.name
  having count(*) > 1;

  
  cursor c2(i_town varchar2, i_grp varchar2) is
  select r.name             Region,
         tow.name           Town,
	 po.name	    Post_Town,
         do.name            Delivery_office,
	 rt.name	    Route,
         t.thorfare_name    Thoroughfare,
         t.thorfare_id      Thoroughfare_Id,
         g.name             Building_Group,
         g.group_id,
         sum(decode(b.group_id,null,0,1))  Numberof_Bldgs
  from   t_building_groups g,
         t_thoroughfares t,
         t_towns tow,
         t_counties c,
         t_regions r,
         t_buildings b,
	 t_routes rt,
	 t_delivery_offices do,
	 t_post_towns po,
         t_head_offices ho
  where  g.name = i_grp
  and    g.thorfare_id = t.thorfare_id
  and    g.group_id    = b.group_id(+)
  and	 g.group_id not in (34136237,34135433,36403684,34601116,33787386,34868047,33509736,33759252,
	36374110,33955496,33505691,33503729,33752669,33259750,33280390,33505635,33790044,35949256,
	36329172,34364053,34359874,33405892,33399920,35395903,35395394,37071240,37071254,34713339,
	34750521,37361658,36014603,37357048,37357052,37608633,37612658,37920299,37872490,33245414,
	33361298,33229760,33485205,33366677,33198406,33302670,37976005,36361695,33367579,32848134,
	34325937,33271271,33384521,33086584,36256395,33157511,33469611,33290623,37609425,37988768,
	37981095,37988948,33053880,33022896,32929021,36743416,33417778,33219633,37989018,37977998,
	36386701,36385807,33087317,34899944,33493998,32876418,33414590,33228571,33039412,36395303,
	33292192,36017861,34609364,33228443,33359986,37761338,33360297,32875686,37390813,37541436,
	37565022,37454284,33149006,33476157,33400392,37978665,33383707,34468149,36378302,37823676,
	33412745,32720090,32912167,37971355,33519910,33520008,33023445,33630578,33432415,33438885,
	33413541,33522175,34796949,33413372,32870323,33336749,32810236,34586140,33414393,36290287,
	33336271,36219573,32875620,33352579,32808768,33414285,32980426,33511600,32811573,33056134,
	36748446,33758192,35925243,33403218,37752722,36242437,34868205,34905984,34237782,34239235,
	33993005,37753140,37751119,37988628,37987632)
  and    t.town_id     = tow.town_id
  and    tow.name      = i_town
  and    b.route_id=rt.route_id(+)
  and	 rt.delivery_office_id=do.delivery_office_id(+)
  and    do.post_town_id=po.post_town_id(+)
  and	 po.ho_id=ho.ho_id
  and    ho.county_id=c.county_id
  and    c.region_id=r.region_id
  and    r.name='MULLINGAR'
  group by r.name,tow.name,po.name,do.name,rt.name,t.thorfare_name,t.thorfare_id,g.name,g.group_id
  order by 3,4,5;

begin
  dbms_output.put_line('"Region","Town","Post Town","Delivery Office","Route","Thoroughfare","Thoroughfare Id","Building Group","Group Id","No.of Bldgs"');
    for c1_rec in c1 loop
    for c2_rec in c2(c1_rec.town,c1_rec.grp) loop
             dbms_output.put_line('"'||c2_rec.region           ||'","'
                              ||c2_rec.town                    ||'","'
                              ||c2_rec.post_town               ||'","'
                              ||c2_rec.delivery_office         ||'","'
                              ||c2_rec.route	               ||'","'
                              ||c2_rec.thoroughfare            ||'",'
                              ||to_char(c2_rec.thoroughfare_id)||',"'
                              ||c2_rec.building_group          ||'",'
                              ||to_char(c2_rec.group_id)       ||','
                              ||to_char(c2_rec.Numberof_bldgs));
    end loop;
  end loop;
end;
/
spool off

exit;


Re: Order by clause doesn't work as wanted [message #291208 is a reply to message #291202] Thu, 03 January 2008 06:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For each row return by cursor c1, the result is sorted by 3, 4, 5.
As cursor c1 is bot ordered, the final result is no more.

If you want to order the whole by 3, 4, 5 you have to embed c2 into c1 and have only 1 cursor (or better one SQL statement and remove all PL/SQL stuff).

Regards
Michel
Re: Order by clause doesn't work as wanted [message #291235 is a reply to message #291208] Thu, 03 January 2008 08:16 Go to previous messageGo to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
Hi Michel
Thanks a million
Can you please tell me a way how to embed c2 into c1?What you mean?I dont have a clue about this

Meanwhile i am trying to make it as 1 sql statement.
Thanks again
Re: Order by clause doesn't work as wanted [message #291241 is a reply to message #291235] Thu, 03 January 2008 09:01 Go to previous messageGo to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
Hi
I tried making the select statement like below
But I can't give the order by clause here as it won't allow me to order by more than 1 column(as the select here is only 1 column as i want the output in csv format)
Pls advise on this - I don't mind if it is using sql pr pl/sql statements
SELECT	DISTINCT '"'||r.name			||'"'||','||
		'"'||tow.name			||'"'||','||
		'"'||po.name			||'"'||','||
		'"'||DO.name		        ||'"'||','||
		'"'||rt.name     		||'"'||','||
                '"'||t.thorfare_name   		||'"'||','||
                '"'||t.thorfare_id     		||'"'||','||
                '"'||g.name     		||'"'||','||
                '"'||g.group_id    		||'"'||','||
		'"'||SUM(DECODE(b.group_id,NULL,0,1))	||'"'
FROM
 T_BUILDING_GROUPS g,
         T_THOROUGHFARES t,
         T_TOWNS tow,
         T_COUNTIES c,
         T_REGIONS r,
         T_BUILDINGS b,
	 T_ROUTES rt,
	 T_DELIVERY_OFFICES DO,
	 T_POST_TOWNS po,
         T_HEAD_OFFICES ho
  WHERE  (g.name,tow.name) IN
  (SELECT g.name,tow.name FROM
    T_BUILDING_GROUPS g,
       T_THOROUGHFARES t,
       T_TOWNS tow
  WHERE g.thorfare_id = t.thorfare_id
  AND   t.town_id     = tow.town_id
  GROUP BY tow.name,g.name
  HAVING COUNT(*) > 1)
  AND    g.thorfare_id = t.thorfare_id
  AND    g.group_id    = b.group_id(+)
  AND	 g.group_id NOT IN (34136237,34135433,36403684,34601116,33787386,34868047,33509736,33759252,
	36374110,33955496,33505691,33503729,33752669,33259750,33280390,33505635,33790044,35949256,
	36329172,34364053,34359874,33405892,33399920,35395903,35395394,37071240,37071254,34713339,
	34750521,37361658,36014603,37357048,37357052,37608633,37612658,37920299,37872490,33245414,
	33361298,33229760,33485205,33366677,33198406,33302670,37976005,36361695,33367579,32848134,
	34325937,33271271,33384521,33086584,36256395,33157511,33469611,33290623,37609425,37988768,
	37981095,37988948,33053880,33022896,32929021,36743416,33417778,33219633,37989018,37977998,
	36386701,36385807,33087317,34899944,33493998,32876418,33414590,33228571,33039412,36395303,
	33292192,36017861,34609364,33228443,33359986,37761338,33360297,32875686,37390813,37541436,
	37565022,37454284,33149006,33476157,33400392,37978665,33383707,34468149,36378302,37823676,
	33412745,32720090,32912167,37971355,33519910,33520008,33023445,33630578,33432415,33438885,
	33413541,33522175,34796949,33413372,32870323,33336749,32810236,34586140,33414393,36290287,
	33336271,36219573,32875620,33352579,32808768,33414285,32980426,33511600,32811573,33056134,
	36748446,33758192,35925243,33403218,37752722,36242437,34868205,34905984,34237782,34239235,
	33993005,37753140,37751119,37988628,37987632)
  AND    t.town_id     = tow.town_id
  AND    b.route_id=rt.route_id(+)
  AND	 rt.delivery_office_id=DO.delivery_office_id(+)
  AND    DO.post_town_id=po.post_town_id(+)
  AND	 po.ho_id=ho.ho_id
  AND    ho.county_id=c.county_id
  AND    c.region_id=r.region_id
  AND    r.name='MULLINGAR'
 GROUP BY r.name,tow.name,po.name,DO.name,rt.name,t.thorfare_name,t.thorfare_id,g.name,g.group_id
Re: Order by clause doesn't work as wanted [message #291242 is a reply to message #291235] Thu, 03 January 2008 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The simplest way (maybe not the most efficient one) is
<c2 select>
from ..., (<c1 select>)
where ...
and join on c1 columns instead of c1 record field

Regards
Michel

Re: Order by clause doesn't work as wanted [message #291245 is a reply to message #291242] Thu, 03 January 2008 09:04 Go to previous messageGo to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
sorry can you make it a little bit more clear, i really don't understand..
Re: Order by clause doesn't work as wanted [message #291248 is a reply to message #291245] Thu, 03 January 2008 09:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try it all the same.
If I want to write more I have to write the whole query and it bores me.

Regards
Michel
Re: Order by clause doesn't work as wanted [message #291254 is a reply to message #291248] Thu, 03 January 2008 09:31 Go to previous messageGo to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
Can you atleast say what do you mean by join on c1 columns instead of c1 record fields?
Re: Order by clause doesn't work as wanted [message #291257 is a reply to message #291254] Thu, 03 January 2008 09:39 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
where g.name = i_grp
...
and tow.name = i_town

These are joins clause between the 2 cursors in c2.

Regards
Michel

Previous Topic: Sequence
Next Topic: ORA-01031: insufficient privileges
Goto Forum:
  


Current Time: Sat Dec 10 22:38:04 CST 2016

Total time taken to generate the page: 0.07801 seconds