sql - Convert Horizontal CSV Template to a Tabular Format -
disclaimer: please bear long, detailed question. want make requirement clear possible.
the below seeded table (sample_extra_info) stores employee's information:
eit_id employee_number extra_information information_category information1 information2 information3 .... information10 ------ ------------------- --------------------- ---------------------- -------------- ------------- ------------ -------------- 1 11111 academic rank next academic rank professor y 10.5 (null) 2 11111 academic rank academic rank ass.professor n (null) (null) 3 11111 academic rank effective start date 01-jan-2017 (null) (null) (null) 4 11111 academic rank effective end date 31-dec-4712 (null) (null) (null) 5 11111 alien income forecast income_code p (null) (null) (null) 6 11111 alien income forecast date 2017 01 dec (null) 7 11111 alien income forecast amount 10000 tax (null) (null) 8 11111 alien income forecast effective start date 01-jan-2017 (null) (null) (null) 9 11111 alien income forecast effective end date 31-dec-4712 (null) (null) (null) 10 22222 academic rank next academic rank master n 11.5 (null) 11 22222 academic rank academic rank professor y (null) (null) 12 22222 academic rank effective start date 01-jan-2017 (null) (null) (null) 13 22222 academic rank effective end date 31-dec-4712 (null) (null) (null) 14 22222 alien income forecast income_code x1 (null) (null) (null) 15 22222 alien income forecast date 2017 01 apr (null) 16 22222 alien income forecast amount 100000000 tax-free (null) (null) 17 22222 alien income forecast effective start date 01-jan-2017 (null) (null) (null) 18 22222 alien income forecast effective end date 31-dec-4712 (null) (null) (null) all information columns have data type varchar2, store dates (like in above sample).
this table being populated seeded api package (sample_pkg.create_extra_info).
sample procedure specification
sample_pkg.create_extra_info ( p_employee_number varchar2 , p_extra_information varchar2 , p_information_category varchar2 , p_information1 varchar2 , p_information2 varchar2 , p_information3 varchar2 , p_information4 varchar2 , p_information5 varchar2 , p_information6 varchar2 , p_information7 varchar2 , p_information8 varchar2 , p_information9 varchar2 , p_information10 varchar2 ); i tasked create upload process use csv template , interface information api. below sample of csv template:
employee_number next academic rank max academic rank max score academic rank promotion rank academic_rank_start_date academic_rank_end_date income_code date(yyyy) date(mon) date(dd) amount tax? income_start_date income_end_date --------------- ------------------ ------------------ ---------- ------------- -------------- ------------------------ ------------------ ----------- --------- --------- --------- ------ ----- ----------------- ------------------- 33333 professor y 10.1 ass.professor y 01-jan-2017 31-dec-4712 p 2017 jan 01 10000 tax 01-jan-2017 31-dec-4712 44444 ass.professor n 9.7 student n 01-jan-2017 31-dec-4712 r 2017 jan 17 50000 non-tax 01-jan-2017 31-dec-4712 i'm planning use external table (sample_extra_info_ext) read csv template , pass information api.
create table sample_extra_info_ext ( employee_number varchar2(250) , next_academic_rank varchar2(250) , max_academic_rank varchar2(250) , max_score varchar2(250) , academic_rank varchar2(250) , promotion_rank varchar2(250) , academic_rank_start_date varchar2(250) , academic_rank_end_date varchar2(250) , income_code varchar2(250) , date_yyyy varchar2(250) , date_mon varchar2(250) , date_dd varchar2(250) , amount varchar2(250) , tax varchar2(250) , income_start_date varchar2(250) , income_end_date varchar2(250) ) organization external ( type oracle_loader default directory ext_tab_data access parameters ( records delimited newline skip 1 fields terminated ',' optionally enclosed '"' lrtrim missing field values null ) location(ext_tab_data: 'demographic_file_10apr2017.csv') ) reject limit unlimited ; and use code block below (please note comments):
declare cursor ext_cur select employee_number , next_academic_rank -- academic rank (next academic rank) information1 , max_academic_rank -- academic rank (next academic rank) information2 , max_score -- academic rank (next academic rank) information3 , academic_rank -- academic rank (academic rank) information1 , promotion_rank -- academic rank (academic rank) information2 , academic_rank_start_date -- academic rank (effective start date) information1 , academic_rank_end_date -- academic rank (effective end date) information1 , income_code -- alien income forecast (income_code) information1 , date_yyyy -- alien income forecast (date) information1 , date_mon -- alien income forecast (date) information2 , date_dd -- alien income forecast (date) information3 , amount -- alien income forecast (amount) information1 , tax -- alien income forecast (amount) information2 , income_start_date -- alien income forecast (effective start date) information1 , income_end_date -- alien income forecast (effective end date) information1 sample_extra_info_ext; -- external table begin ext_rec in ext_cur loop sample_pkg.create_extra_info ( p_employee_number => ext_rec.employee_number , p_extra_information => ext_rec.extra_information -- problem, how make vertical? , p_information_category => ext_rec.information_category , p_information1 => ext_rec.information1 , p_information2 => ext_rec.information2 , p_information3 => ext_rec.information3 , p_information4 => ext_rec.information4 , p_information5 => ext_rec.information5 , p_information6 => ext_rec.information6 , p_information7 => ext_rec.information7 , p_information8 => ext_rec.information8 , p_information9 => ext_rec.information9 , p_information10 => ext_rec.information10 ); end loop; end; here problems:
- the template "horizontal", api "vertical".
- not information use same information columns (some use column information1 - 3, use column information1 ).
with said, how can transform template above valid format api can use?
i thinking of using pivot or unpivot in external table i'm not quite sure on how approach requirement. pure sql approach preferred, using pl/sql fine.
oracle version
oracle database 11g enterprise edition release 11.2.0.4.0 - 64bit production pl/sql release 11.2.0.4.0 - production "core 11.2.0.4.0 production" tns solaris: version 11.2.0.4.0 - production nlsrtl version 11.2.0.4.0 - production
it looks me need select many times same table, using different column depending on info gathering. if template static, here gives code of ext_cur cursor:
cursor ext_cur select employee_number, 'academic rank' extra_information, 'next academic rank' information_category, [... fetching various columns] sample_extra_info_ext union select employee_number, 'academic rank' extra_information, 'academic rank' information_category, [... fetching various columns] sample_extra_info_ext union select employee_number, 'academic rank' extra_information, 'effective start date' information_category, [... fetching various columns] sample_extra_info_ext union select employee_number, 'academic rank' extra_information, 'effective end date ' information_category, [... fetching various columns] sample_extra_info_ext union select employee_number, 'alienif' extra_information, 'income_code ' information_category, [... fetching various columns] sample_extra_info_ext union select employee_number, 'alienif' extra_information, 'date ' information_category, [... fetching various columns] sample_extra_info_ext union select employee_number, [and goes on whatever line api needs] here working example illustrate
with t ( select '33333 ' employee_number , 'professor ' "next academic rank " , 'y ' "max academic rank " , '10.1 ' "max score " ,'ass.professor' "academic rank " ,'y ' "promotion rank" ,'01-jan-2017 ' "academic_rank_start_date" ,'31-dec-4712 ' "academic_rank_end_date" ,'p ' "income_code " ,'2017 ' "date(yyyy)" ,' jan ' "date(mon)" ,' 01 ' "date(dd)" ,' 10000 ' " amount " ,' tax ' "tax?" ,'01-jan-2017 ' "income_start_date " ,' 31-dec-4712 ' " income_end_date" dual union select '44444 ', 'ass.professor ', 'n ', '9.7 ','student ','n ','01-jan-2017 ','31-dec-4712 ','r ','2017 ',' jan ',' 17 ',' 50000 ',' non-tax ','01-jan-2017 ',' 31-dec-4712 ' dual ) select employee_number, 'academic rank' extra_information, 'next academic rank' information_category, "next academic rank " information1 , null information2 , null information3 t union select employee_number, 'academic rank' extra_information, 'academic rank' information_category, "academic rank " information1 , null information2 , null information3 t union select employee_number, 'academic rank' extra_information, 'effective start date' information_category, "academic_rank_start_date" information1 , null information2 , null information3 t union select employee_number, 'academic rank' extra_information, 'effective end date ' information_category, "academic_rank_end_date" information1 , null information2 , null information3 t union select employee_number, 'alienif' extra_information, 'income_code ' information_category, "income_code " information1 , null information2 , null information3 t union select employee_number, 'alienif' extra_information, 'date ' information_category, "date(yyyy)" information1 , "date(mon)" information2 , "date(dd)" information3 t order 1, 2, 3 leading
if template changes time becomes different problem. must learn how build cursor dynamic pl/sql. there's plenty of resources here on so. that's pretty big chunk of work, uses same idea above.
Comments
Post a Comment