Cursor Help

[pashu]

Hello Friends,

I'm having a table name emp_trn whose structure is below
temp_no number(4),
tbasic number(14,2),
tperpay number(14,2),
tda number(14,2),
tldpay number(14,2),
tspay number(14,2),
tsal_pfbase number(20,2),
thra number(16,2),
tfoodall number(14,2),
tconvall number(14,2),
teduall number(14,2),
tadjall number(14,2),
tscall number(14,2),
tmedall number(14,2),
tmiscall number(14,2),
tmisc_all number(14,2),
tgrdall number(14,2),
titax number(16,2),
tgross_ear number(20,2),
tnet_earn number(20,2),
tnet_pay number(20,2),
ttot_ded number(16,2),
mth varchar2(2),
yr varchar2(4),
mth_name varchar2(20),
mon_days number(4),
paid_days number(4,2),
tpf number(14,2),
tfpf number(14,2),
teps number(14,2),
tesic number(14,2),
tesic_emplyr number(14,2),
tpt number(10,2),
tpv_adj number(8,2),
tcr_adj number(8,2),
tcant number(10,2),
tsoc number(10,2),
tfest number(10,2),
tlwf number(4),
netpay_words varchar2(200),
mth_id number(2),
arrears char(1),
taddpf number(10,2),

The above table contains the salary details of every month.
I want to extract the following fields from above table temp_no,paid_days,mth_id,mth,yr,tesic,tgross_ear of an employee occording to month (mth) and year(yr) and insert into the foll table esic_dtl -
emp_no number(4),
emp_fname varchar(35),
esic_no number(10),
esic_aprmth varchar2(20),
esic_aprmthid number(2),
esic_apryr varchar2(4),
esic_aprwages number(8,2),
esic_aprpdays number(4,2),
esic_apremp number(8,2),
esic_apremplyr number(8,2),
esic_maymth varchar2(20),
esic_maymthid number(2),
esic_mayyr varchar2(4),
esic_maywages number(8,2),
esic_maypdays number(4,2),
esic_mayemp number(8,2),
esic_mayemplyr number(8,2),
esic_junmth varchar2(20),
esic_junmthid number(2),
esic_junyr varchar2(4),
esic_junwages number(8,2),
esic_junpdays number(4,2),
esic_junemp number(8,2),
esic_junemplyr number(8,2),
esic_julmth varchar2(20),
esic_julmthid number(2),
esic_julyr varchar2(4),
esic_julwages number(8,2),
esic_julpdays number(4,2),
esic_julemp number(8,2),
esic_julemplyr number(8,2),
esic_augmth varchar2(20),
esic_augmthid number(2),
esic_augyr varchar2(4),
esic_augwages number(8,2),
esic_augpdays number(4,2),
esic_augemp number(8,2),
esic_augemplyr number(8,2),
esic_sepmth varchar2(20),
esic_sepmthid number(2),
esic_sepyr varchar2(4),
esic_sepwages number(8,2),
esic_sepdays number(4,2),
esic_sepemp number(8,2),
esic_sepemplyr number(8,2)
The above table contains esic details of an employee from apr to sep.
The row in the above table should look like-
apr may jun
empno name paid_days esic gross | paid_days esic gross
1 xyz 30 100 6000 | 31 50 5000

and subsequently till sep.
Please help me how should the values be extracted using cursor.Waiting for ur valuable reply.Thanks in advance.

Pashu

 

[SantaMufasa]

Pashu,

If you are starting with the contents of table "emp_trn", which contains all of your data in the first place (and is fairly normalised), why would you want to degrade the table into badly disobeying First Normal Form (1NF), which says "avoid repeating groups" (APR, MAY, JUN, JULY, et cetera)? If you are already using PL/SQL cursors, your code would be much easier written if you access table "emp_trn" (using a CURSOR "FOR LOOP") than writing contrived code against a contrived table such as your "esic_dtl" table.

Let us know your thoughts,

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:32 (23Apr04) UTC (aka "GMT" and "Zulu"), 10:32 (23Apr04) Mountain Time)

 

[pashu]

Thanks Mufasa,

What u say is correct i don't mind fetching the details from emp_trn thru cursor,can u help me in coding.

Thanks & Regards,
Pashu.

 

[pashu]

Dear SantaMufasa,

Please can u send the pl/sql code for the above problem.

Thanks & Regards,
Prashant.

 

[SantaMufasa]

Pashu,

Yes, I shall send you PL/SQL code as soon as I can, but since 08:00 this morning, I have been installing, migrating, and importing 3 major databases from Oracle 8.1.x to 9.2.0.4 simultaneously at three different sites, so my "mental CPU" is tied up presently. As soon as I finish with this assignment (which I must do before workers start showing up for work on Monday a.m.) I shall build some code for you from the structures you posted, above.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA @ 03:07 (25Apr04) UTC (aka "GMT" and "Zulu"), 20:07 (24Apr04) Mountain Time)