Home » SQL & PL/SQL » SQL & PL/SQL » Change of Order BY clause in Cursor
Change of Order BY clause in Cursor [message #243134] Wed, 06 June 2007 03:58 Go to next message
sjaiswal
Messages: 31
Registered: September 2006
Location: Mumbai
Member
Hi
I have a query where I am using a cursors with Order By clause on a column. In my pl/sql block, I need to use the same cursor but with the Order By in different order.
First I need the column 'sdate' in Asc order and at other time I need to use the same cursor data but in descending order on the 'sdate' column.
I can write the whold cursor code again with descending ORDER but the script now itself is very comlicated and using the cursor again will make the script more bulky.
Is there a way to reuse the cursor only by changeing the order.

Please advice.

Soni
Re: Change of Order BY clause in Cursor [message #243140 is a reply to message #243134] Wed, 06 June 2007 04:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a little more on what you want to do, maybe in pseudo-code.
Also post your Oracle version.

Regards
Michel
Re: Change of Order BY clause in Cursor [message #243142 is a reply to message #243140] Wed, 06 June 2007 04:09 Go to previous messageGo to next message
sjaiswal
Messages: 31
Registered: September 2006
Location: Mumbai
Member
I have to do some updates and inserts based on different conditions in a LOOP.
The cursor is as below :
FOR l_rec IN (SELECT /*+ RULE */  item_id, item,
                       po_order_no, ln_shp_qty, date_ln_arr,
                       date_ln_eta_dest, ln_container, ln_vehicle_name,
                       ln_destination, wh_update_date_id,
                       COUNT(po_order_no) OVER (PARTITION BY item_id,
                         po_order_no ORDER BY item_id, po_order_no,
                         date_ln_arr, date_ln_eta_dest, ln_container,
                         ln_vehicle_name, ln_destination) srno2,
                       COUNT(po_order_no) OVER (PARTITION BY item_id,
                         po_order_no) cnt2
                  FROM (
                        SELECT lognet.item_id, s.item, po_order_no,ln_shp_qty,
                               CASE WHEN LENGTH(date_ln_arr) = 6 THEN
                                    TO_DATE(SUBSTR(date_ln_arr,2),'RRDDD')
                                    WHEN LENGTH(date_ln_arr) = 7 THEN
                                    TO_DATE(SUBSTR(date_ln_arr,3),'RRDDD')
                               END date_ln_arr, date_ln_eta_dest, ln_container,
                               ln_vehicle_name,ln_destination,MAX(wh_update_date_id)wh_update_date_id
                          FROM bidw.fact_lognet@rowhs.world lognet,
                               bidw.lu_item@rowhs.world litem,
                               springs.si_jde_raw_item i,
                               stsc.schedrcpts s
                         WHERE lognet.item_id = litem.item_id
                           AND i.item = litem.item_number_2
                           AND s.scen = 0
                           AND s.item = i.sku
                           AND po_order_no = s.si_jde_order_num
                           AND wh_update_date_id IN 
                                 (SELECT MAX(wh_update_date_id)
                                    FROM bidw.fact_lognet@rowhs.world lognet
                                   GROUP BY item_id, po_order_no)
                         GROUP BY lognet.item_id, s.item, po_order_no,
                               ln_shp_qty, date_ln_arr, date_ln_eta_dest,
                               ln_container, ln_vehicle_name, ln_destination
                       )
               )
    LOOP
      FOR s_rec IN (SELECT s.ROWID rid, s.item, scheddate, loc,
                             cppprodmethod, lastcompletedstep, pctcomplete,
                             reviseddate, expdate, si_po_orddate, si_po_type,
                             si_vend_shpdate, si_last_recpt_date,
                             si_purchase_price, si_supplier_name,
                             si_supplier_nbr, si_jde_order_num,
                             s.si_tot_units_recv,
                             COUNT(si_jde_order_num)
                                OVER (PARTITION BY s.item, si_jde_order_num
                               ORDER BY s.item, si_jde_order_num,
                                        scheddate, s.ROWID) srno1,
                             COUNT(si_jde_order_num) OVER
                             (PARTITION BY s.item, si_jde_order_num, scheddate)cnt1
                        FROM stsc.schedrcpts s
                       WHERE s.scen = 0
                         AND s.item = l_rec.item
                         AND s.si_jde_order_num = l_rec.po_order_no
                         AND s.supplysource = 3
                         AND s.keep_row = 0
                         AND NVL(TRIM(s.si_alt_item),' ') IN ('COPIED',' ')
                       ORDER BY s.item, si_jde_order_num, scheddate     
                   )
        LOOP

IN this loop I have to update a table based on the some condition where the l_rec.date_ln_arr should be in asc order which is coming but I also to update some records in the same where the records should be in descending order of l_rec.date_ln_arr as I have to update with the most corrent date for one or more rows.

So I will need to use the above cursor where I can get the above data but in different order of date.
But since this is big code, I was asking for some way to reuse the above cursor to get date column in different order.
Please advice.

Thanks,
Soni
Re: Change of Order BY clause in Cursor [message #243156 is a reply to message #243140] Wed, 06 June 2007 04:46 Go to previous messageGo to next message
techno
Messages: 44
Registered: October 2003
Member
You can pass a parameter to cursor which changes the order by clause by the use of CASE like this...


declare
cursor c1(myption in number) is
select * from emp
order by case when myption =1 then sdate else (-1*sdate) end;
begin
for c1r in c1(1) loop
dbms_output.put_line(c1r.ename||c1r.eno||c1r.sdate);
end loop;
end;



if you pass 1 to the cursor the sdate column is sorted in Ascending and for any other value it is sorted in descending


Regards
Techno
Re: Change of Order BY clause in Cursor [message #243170 is a reply to message #243156] Wed, 06 June 2007 05:49 Go to previous messageGo to next message
sjaiswal
Messages: 31
Registered: September 2006
Location: Mumbai
Member
Thanks for your reply Techno,
I got the data in different order as you said.
I am now trying to apply it on my script


Thanks,
Soni

[Updated on: Wed, 06 June 2007 05:54]

Report message to a moderator

Re: Change of Order BY clause in Cursor [message #243319 is a reply to message #243170] Wed, 06 June 2007 21:42 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Or you could create a view.

Ross Leishman
Re: Change of Order BY clause in Cursor [message #243370 is a reply to message #243156] Thu, 07 June 2007 02:20 Go to previous messageGo to next message
sjaiswal
Messages: 31
Registered: September 2006
Location: Mumbai
Member
Hi Techno,
I tried this on my script, but the column to be sorted is a DATE column and its giving error in case when its a DATE column ELSE it runs fine.

  1  declare
  2  cursor c1(myption in number) is
  3  select * from emp
  4  order by case when myption =1 then hiredate else (-1*hiredate) end;
  5  begin
  6  for c1r in c1(1) loop
  7  dbms_output.put_line(c1r.ename||c1r.empno||c1r.hiredate);
  8  end loop;
  9* end;
SQL> /
select * from emp
       *
ERROR at line 3:
ORA-06550: line 3, column 8:
PL/SQL: ORA-00932: inconsistent datatypes
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 7, column 22:
PLS-00364: loop index variable 'C1R' use is invalid
ORA-06550: line 7, column 1:
PL/SQL: Statement ignored


Please let me know what to do in case when the column to be sorted is a DATE column.

Thanks,
Soni
Re: Change of Order BY clause in Cursor [message #243380 is a reply to message #243370] Thu, 07 June 2007 02:46 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You cannot have negative dates.

order by case when myption =1 then (hiredate-sysdate) else (sysdate-hiredate) end;


Or, like I suggested earlier, use a view.

Ross Leishman
Re: Change of Order BY clause in Cursor [message #243388 is a reply to message #243134] Thu, 07 June 2007 03:19 Go to previous message
sjaiswal
Messages: 31
Registered: September 2006
Location: Mumbai
Member
Thanks rleishman,
I got the script running.

Regards,
Soni
Previous Topic: Record count
Next Topic: exclude a group of records
Goto Forum:
  


Current Time: Sun Dec 04 02:38:22 CST 2016

Total time taken to generate the page: 0.15163 seconds