All geek questions in one place

Convert horizontal CSV template to table format

Disclaimer: Please accept the long detailed question. I want to make this requirement as clear as possible.

The following table ( SAMPLE_EXTRA_INFO) stores additional employee 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 a data type VARCHAR2, but some retention dates (for example, in the example above).

This table is populated with the seeded API package ( SAMPLE_PKG.CREATE_EXTRA_INFO).

Test 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 have been tasked with creating a boot process that will use the CSV template and interface information for the API. The following is a sample 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 plan to use an external table ( SAMPLE_EXTRA_INFO_EXT) to read the CSV template and pass the information to the 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 by newline
                            skip 1
                            fields terminated by ','
                            optionally enclosed by '"' LRTRIM
                            missing field values are null                            
                        )
                        location(EXT_TAB_DATA: 'Demographic_file_10APR2017.csv')
                       ) reject limit unlimited
                       ;

- ( ):

DECLARE

    CURSOR EXT_CUR IS
    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                          
    FROM    SAMPLE_EXTRA_INFO_EXT;    -- this is the external table

BEGIN

    FOR 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    -- This is my problem, how do i make it 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;

:

  • "", API "".
  • ( INFORMATION1 - 3, INFORMATION1).

, , API ?

Pivot UnPivot , , . Pure SQL, PL/SQL .

Oracle

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 for Solaris: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
+6
1

, , , , . , EXT_CUR :

CURSOR EXT_CUR IS
          select employee_number, 'Academic Rank' EXTRA_INFORMATION,   'Next Academic Rank'   INFORMATION_CATEGORY, [... fetching various columns]  from SAMPLE_EXTRA_INFO_EXT
union all select employee_number, 'Academic Rank' EXTRA_INFORMATION,   'Academic Rank'        INFORMATION_CATEGORY, [... fetching various columns]  from SAMPLE_EXTRA_INFO_EXT
union all select employee_number, 'Academic Rank' EXTRA_INFORMATION,   'Effective Start Date' INFORMATION_CATEGORY, [... fetching various columns]  from SAMPLE_EXTRA_INFO_EXT
union all select employee_number, 'Academic Rank' EXTRA_INFORMATION,   'Effective End Date '  INFORMATION_CATEGORY, [... fetching various columns]  from SAMPLE_EXTRA_INFO_EXT
union all select employee_number, 'AlienIF'       EXTRA_INFORMATION,   'Income_code '         INFORMATION_CATEGORY, [... fetching various columns]  from SAMPLE_EXTRA_INFO_EXT
union all select employee_number, 'AlienIF'       EXTRA_INFORMATION,   'Date        '         INFORMATION_CATEGORY, [... fetching various columns]  from SAMPLE_EXTRA_INFO_EXT
union all select employee_number, [and it goes on for whatever line your API needs]


with t as (  
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"
from 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    ' from dual
)         select employee_number, 'Academic Rank' EXTRA_INFORMATION,   'Next Academic Rank'   INFORMATION_CATEGORY,  "Next Academic Rank "         INFORMATION1 , null         INFORMATION2 , null       INFORMATION3 from t 
union all select employee_number, 'Academic Rank' EXTRA_INFORMATION,   'Academic Rank'        INFORMATION_CATEGORY,  "Academic Rank "              INFORMATION1 , null         INFORMATION2 , null       INFORMATION3 from t 
union all select employee_number, 'Academic Rank' EXTRA_INFORMATION,   'Effective Start Date' INFORMATION_CATEGORY,  "ACADEMIC_RANK_START_DATE"    INFORMATION1 , null         INFORMATION2 , null       INFORMATION3 from t 
union all select employee_number, 'Academic Rank' EXTRA_INFORMATION,   'Effective End Date '  INFORMATION_CATEGORY,  "ACADEMIC_RANK_END_DATE"      INFORMATION1 , null         INFORMATION2 , null       INFORMATION3 from t 
union all select employee_number, 'AlienIF'       EXTRA_INFORMATION,   'Income_code '         INFORMATION_CATEGORY,  "Income_code "                INFORMATION1 , null         INFORMATION2 , null       INFORMATION3 from t 
union all select employee_number, 'AlienIF'       EXTRA_INFORMATION,   'Date        '         INFORMATION_CATEGORY,  "Date(YYYY)"                  INFORMATION1 , "Date(MON)"  INFORMATION2 , "Date(DD)" INFORMATION3 from t 
order by 1, 2, 3

enter image description here

, . PL/SQL. SO. , , .

+1

Source: https://habr.com/ru/post/1016350/

More articles:


All Articles