Home » SQL & PL/SQL » SQL & PL/SQL » cursor query
cursor query [message #267328] Thu, 13 September 2007 01:06 Go to next message
Yasir Hashmi
Messages: 304
Registered: April 2006
Senior Member
This is my code
declare
   tcode varchar2(4);
    sysid number:=1;
    mhead varchar2(4);
   -- grossamt varchar2(100);
    mjhead varchar2(4);
entdate date;
begin
for var in (select trea_code,op_code,chal_no,chal_date,depname,address
,[B]h_code[/B],col_code,div_code,chal_amt,src_mjcd,rem,ent_date,
ent_time,reg_no,purpose,rec_per,pr_amt,int_amt,ins_no,slr_no
,pay_type from rec_cha order by substr(h_code,1,4 ) 


loop
 
tcode:=to_number(substr(var.trea_code,1,2));
[B] mhead:= substr(var.h_code,1,4);[/B]
insert into t_chln_hdr
values(sysid,tcode,mhead,var.ent_date,var.ent_date,user,var.ent_date,0,' ',' ','X'
,' ',SYSDATE); 

insert into t_chln
values(sysid,var.chal_no,'SCTN','BOOK','CHTYP',' ',
var.chal_date,'N','X','X','N',
'N',1,'E',USER,VAR.ENT_DATE,' ','E',' ',0,' ','N','B',' ',
var.chal_amt,var.chal_amt,0,' ','C','G',' ','P',' ',user,VAR.ent_date,' ');

 insert into t_chln_amnt
 values(var.chal_no,sysid,substr(var.h_code,1,9)||'  '||substr(var.h_code,10,2)
||'  '||substr(var.h_code,14,2),' ', var.chal_amt,'O',' ',
SYSDATE,' ',' ',' ',' ',
' ',SYSDATE,' ');
   
[B]if mhead !=substr(var.h_code,1,4) then
sysid:=sysid+1;
end if;[/B]
end loop;
COMMIT;
end;


I want the outout for example as:




mhead       sysid                  
------      ------    
10              1
10              1
20              2
20              2
20              2

...and so on.

I am trying but not getting the desired output.



Thanks
Re: cursor query [message #267331 is a reply to message #267328] Thu, 13 September 2007 01:08 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>I am trying but not getting the desired output
So keep trying until you get what you want.
Re: cursor query [message #267337 is a reply to message #267328] Thu, 13 September 2007 01:14 Go to previous messageGo to next message
Yasir Hashmi
Messages: 304
Registered: April 2006
Senior Member
Can you help in trying?
Re: cursor query [message #267370 is a reply to message #267337] Thu, 13 September 2007 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First make your code output something, there it does not display anything.
And indent it, it is hardly readable.

Regards
Michel
Re: cursor query [message #267389 is a reply to message #267328] Thu, 13 September 2007 03:09 Go to previous messageGo to next message
Yasir Hashmi
Messages: 304
Registered: April 2006
Senior Member
here is a sample table with sample data and the desired ouput.
Once i know the logic, iwill implement it into my original query.

create table chln_hdr( mjh varchar2(4), id number(10));
create table rec_cha(h_code varchar2(15));

data for rec_cha:
10
10
10
10
10
20
20
20
30
30

sample output for chln_hdr:
mjh                  id
10                    1
10                    1 
10                    1
10                    1
10                    1   
20                    2   
20                    2
20                    2
30                    3
30                    3

declare
     sysid number:=1;
   mj number:= 1111;
begin
for var in (select h_code from rec_cha)loop
 insert into chln_hdr values(var.h_code, sysid);
  if  var.h_code !=mj then
 sysid:=sysid+1;
 mj:=var.h_code;
end if;
end loop;
end;

But not getting the desired output.

[Updated on: Thu, 13 September 2007 03:18]

Report message to a moderator

Re: cursor query [message #267409 is a reply to message #267389] Thu, 13 September 2007 04:03 Go to previous message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Would SQL (instead of PL/SQL) do the job?
INSERT INTO chln_hdr 
  (mjh, id)
  (SELECT h_code, dense_rank() over (ORDER BY h_code)
   FROM rec_cha
  );
Previous Topic: struck up with UNION
Next Topic: errors in procedure
Goto Forum:
  


Current Time: Wed Dec 07 07:09:23 CST 2016

Total time taken to generate the page: 0.12264 seconds