SQL & PL/SQL

Hi All,

I have table test_psn which having 7 columns all are varchar2:

create table test_psn(code varchar2(20), first_yesr varchar2(20), val1 varchar2(20),

val2 varchar2(20), val3 varchar2(20), val4 varchar2(20), val5 varchar2(20));

insert into test_psn values ('P0021745','12.25','2.99','16.14','23.04','11.98',);

insert into test_psn values ('P0017106','-6.8','34.1','20.45','2.81','6.94',);

insert into test_psn values ('P0017128','-4.83','-999','18.17','7','9.4',);

insert into test_psn values ('P0018255','1.48','-3.26','5.2','1.9','1.15',);

insert into test_psn values ('P0018263','1.48','-3.26','','1.9','1.15',);

insert into test_psn values ('P0018246','1.48','-3.26','5.2','1.9','1.15',);

insert into test_psn values ('P0000004','','-1.2','-5.75','17.3','-3.21',);

insert into test_psn values ('P0000005','2.43','-1.71','-6.58','14.93','-5.29',);

insert into test_psn values ('P0000010','4.19','-999','-5.47','16.03','-4.3',);

insert into test_psn values ('P0022677','2.36','-0.45','-11.55','7.04','4.53',);

below is the sample data present in above table:

Now I want create a new table which will have only threecolumns like

create table main_table(code varchar2(20), processed_date date, returns number);

Logic is to insert rows into main_table from test_psn table is below:

Code column's value should be the same in both tables, processed_date value should be a proper date like :

if firstyear column value of test_psn table for particular code is 2010 then our processed_date values should be '31-Mar-2010' and corresponding returns column's values should be val1 values.

here processed_date always start with 31-Mar  and year should be firstyear columns's value. and for each val column values processed date should increased by one quarter, below is the sample output for 'P0021754' code:

Here if the value of any val column is either null or -99 then in that case we need to skip that quarter, below is sample data for P0017128 code:

Thanks,