Home » SQL & PL/SQL » SQL & PL/SQL » subscript error
subscript error [message #423997] Tue, 29 September 2009 18:58 Go to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
This time I would try to give clear examples

Here is a table i am working on and the data


create table wmcc(id number, audit_id number, col varchar2(3), c_tm date, u_tm date)
/
insert into wmcc values(3,6,'a',to_date('09/01/2009','MM/DD/YYYY'),null)
/
insert into wmcc values(3,5,'b',to_date('06/01/2009','MM/DD/YYYY'),null)
/
insert into wmcc values(2,4,'a',to_date('07/04/2009','MM/DD/YYYY'),to_date('09/01/2009','MM/DD/YYYY'))
/
insert into wmcc values(2,3,'b',to_date('09/01/2009','MM/DD/YYYY'),to_date('08/04/2009','MM/DD/YYYY'))
/
insert into wmcc values(2,2,'c',to_date('08/04/2009','MM/DD/YYYY'),null)
/
insert into wmcc values(2,1,'c',to_date('05/04/2009','MM/DD/YYYY'),null)
/

insert into wmcc values(3,7,'a',to_date('06/14/2009','MM/DD/YYYY'),to_date('09/01/2009','MM/DD/YYYY'))
/


select id, audit_id,col,c_tm,u_tm from wmcc 
order by id, audit_id desc 



You give above query and observe how the data is:

each audit_id's u_tm = previous audit_id's c_tm

For some reason we have u_tm as nulls for a few rows, i have to
fill in them in this way:

GOAL:
for each id and audit_id , if u_tm is null, pick up the c_tm
of the previous audit_id for that id. (do this until last - 1
audit_id)..Please give the sql i gave and observe the data,


is there any sql to solve this? i went for plsql, here is my proc


/* Formatted on 2009/09/29 17:27 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE p_try
IS
  

   TYPE rec IS RECORD (
      id  wmcc.id%type,
      audit_id wmcc.audit_id%type,
      col  wmcc.col%type,
      c_tm wmcc.c_tm%type,
      u_tm wmcc.u_tm%type
   );

   TYPE t_mt_rec IS TABLE OF rec;

   vt_mt_rec    t_mt_rec;
BEGIN
   SELECT   mt.id,
            mt.audit_id,
              mt.col,
            mt.c_tm,
            mt.u_tm
             BULK COLLECT INTO vt_mt_rec
       FROM wmcc mt
      WHERE mt.id IS NOT NULL
   ORDER BY mt.id, mt.audit_id DESC;



   IF vt_mt_rec.COUNT > 0
   THEN
      FOR i IN vt_mt_rec.FIRST .. vt_mt_rec.COUNT
      LOOP
        if vt_mt_rec (i).u_tm is null then
        update wmcc set u_tm = vt_mt_rec(i+1).c_tm;
       end if;
      END LOOP;
   END IF;
   
   COMMIT;
END;
/


but when i execute this, i get ORA-06533 subscript beyond count
error


Re: subscript error [message #423998 is a reply to message #423997] Tue, 29 September 2009 19:22 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>but when i execute this, i get ORA-06533 subscript beyond count
I am not surprised. Error is expected given the following:

>FOR i IN vt_mt_rec.FIRST .. vt_mt_rec.COUNT
>update wmcc set u_tm = vt_mt_rec(i+1).c_tm;

i goes to COUNT & therefore i+1 throws error as shown below

SQL> EXEC P_TRY;
BEGIN P_TRY; END;

*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at "DBADMIN.P_TRY", line 34
ORA-06512: at line 1

Re: subscript error [message #423999 is a reply to message #423997] Tue, 29 September 2009 19:26 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
i tried this, but did not get the expected result
update wmcc a
set u_tm = decode (u_tm,null,
(select c_tm from wmcc 
where audit_id = (select audit_id - 1 from wmcc b where a.AUDIT_ID = b.AUDIT_ID)),
u_tm)

so do you think i should do i - 1 ?

[Updated on: Wed, 30 September 2009 00:59] by Moderator

Report message to a moderator

Re: subscript error [message #424000 is a reply to message #423999] Tue, 29 September 2009 19:32 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>so do you think i should do i - 1 ?


>TYPE t_mt_rec IS TABLE OF rec;
Superfluous & source of heartburn so eliminate it because it adds nothing to the eventual solution.
Re: subscript error [message #424001 is a reply to message #423997] Tue, 29 September 2009 19:55 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member

/* Formatted on 2009/09/29 17:27 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE p_try
IS
  

   TYPE rec IS RECORD (
      id  wmcc.id%type,
      audit_id wmcc.audit_id%type,
      col  wmcc.col%type,
      c_tm wmcc.c_tm%type,
      u_tm wmcc.u_tm%type
   );

   TYPE t_mt_rec IS TABLE OF rec;

   vt_mt_rec    t_mt_rec;
BEGIN
   SELECT   mt.id,
            mt.audit_id,
              mt.col,
            mt.c_tm,
            mt.u_tm
             BULK COLLECT INTO vt_mt_rec
       FROM wmcc mt
      WHERE mt.id IS NOT NULL
   ORDER BY mt.id, mt.audit_id DESC;



   IF vt_mt_rec.COUNT > 0
   THEN
      FOR i IN vt_mt_rec.FIRST .. vt_mt_rec.COUNT - 1
      LOOP
        if vt_mt_rec (i).u_tm is null then
        update wmcc set u_tm = vt_mt_rec(i+1).c_tm
         where id =vt_mt_rec (i).id and audit_id = vt_mt_rec (i).audit_id ;
       end if;
      END LOOP;
   END IF;
   
   COMMIT;
END;
/


I am almost there...the above gives me updated rows for
all but last but one

i need this to repeat for each id



Re: subscript error [message #424002 is a reply to message #423997] Tue, 29 September 2009 20:04 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
can you please try the above code,

it works, but i dont want data for 2 1 c and 3 5 b
Re: subscript error [message #424003 is a reply to message #424002] Tue, 29 September 2009 20:14 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>can you please try the above code,
You have not shown/describe the expected or desired results.

>it works, but i dont want data for 2 1 c and 3 5 b
I do not understand the line above.

Posting GUIDELINES state you should be using CUT & PASTE,
so we can see what you do & how Oracle responds.

Re: subscript error [message #424004 is a reply to message #423997] Tue, 29 September 2009 20:18 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
how can i show toad output here ?
Re: subscript error [message #424005 is a reply to message #423997] Tue, 29 September 2009 20:28 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
can anyone tell me how to post toad output here, i know i am almost there....just a minor adjustment to my program would
fix this...


Re: subscript error [message #424006 is a reply to message #423997] Tue, 29 September 2009 20:46 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
anybody home ? Sad
Re: subscript error [message #424013 is a reply to message #423997] Tue, 29 September 2009 22:07 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
i got the result, but it runs slower considering millions
of records

is there a way to speed up this ?


CREATE OR REPLACE PROCEDURE p_try
IS
  

   TYPE rec IS RECORD (
      id  wmcc.id%type,
      audit_id wmcc.audit_id%type,
      col  wmcc.col%type,
      c_tm wmcc.c_tm%type,
      u_tm wmcc.u_tm%type
   );

   TYPE t_mt_rec IS TABLE OF rec;

   vt_mt_rec    t_mt_rec;
BEGIN
   SELECT   mt.id,
            mt.audit_id,
              mt.col,
            mt.c_tm,
            mt.u_tm
             BULK COLLECT INTO vt_mt_rec
       FROM wmcc mt
      WHERE mt.id IS NOT NULL
   ORDER BY mt.id, mt.audit_id DESC;



   IF vt_mt_rec.COUNT > 0
   THEN
      FOR i IN vt_mt_rec.FIRST .. vt_mt_rec.COUNT - 1
      LOOP
        if vt_mt_rec (i).u_tm is null and vt_mt_rec(i).id =vt_mt_rec(i+1 ).id  then
        update wmcc set u_tm = vt_mt_rec(i+1).c_tm
         where id =vt_mt_rec (i).id and audit_id = vt_mt_rec (i).audit_id ;
       end if;
      END LOOP;
   END IF;
   
   COMMIT;
END;
/

Re: subscript error [message #424015 is a reply to message #423997] Tue, 29 September 2009 22:31 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
can anyone help me speed up above code ?

can i use forall?
Re: subscript error [message #424017 is a reply to message #423997] Tue, 29 September 2009 22:43 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
someone please advise, i really need to speed this up
Re: subscript error [message #424058 is a reply to message #424017] Wed, 30 September 2009 02:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Nobody over here is getting paid to answer your questions. 9 posts in 3 hours, 5 of them asking if there's anyone there is just being cheeky.

Does this piece of SQL get you the results you're looking for?
If not, please post the results that you're looking for from the test data that you posted.
select id
      ,audit_id
      ,col
      ,c_tm
      ,u_tm
      ,last_value(c_tm ignore nulls) over (partition by id order by audit_id rows between unbounded preceding and 1 preceding) prev_c_tm
from wmcc
order by id, audit_id desc ;
Re: subscript error [message #424193 is a reply to message #423997] Wed, 30 September 2009 08:58 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
I tried this


update wmcc
set u_tm = last_value(c_tm ignore nulls) over (partition by id order by audit_id rows between unbounded preceding and 1 preceding)


since i had to update, it gave no windows function allowed here..
Re: subscript error [message #424196 is a reply to message #424193] Wed, 30 September 2009 09:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'll assume that my query is giving the correct results, as you didn't bother to mention one way or the other.
merge into wmcc  src
using (select id
              ,audit_id
              ,col
              ,c_tm
              ,u_tm
              ,last_value(c_tm ignore nulls) over (partition by id order by audit_id rows between unbounded preceding and 1 preceding) prev_c_tm
        from wmcc) dat
on (src.id = dat.id and src.audit_id = dat.audit_id)
when matched then update set u_tm = prev_c_tm;

Re: subscript error [message #424201 is a reply to message #423997] Wed, 30 September 2009 12:27 Go to previous message
ora1980
Messages: 251
Registered: May 2008
Senior Member
it works, thank you
Previous Topic: How can we get row x to row y in a resultset using SQL Standard?
Next Topic: sum of time
Goto Forum:
  


Current Time: Sun Dec 11 04:25:32 CST 2016

Total time taken to generate the page: 0.07467 seconds