6 Replies Latest reply: May 6, 2013 11:47 AM by 1003788 RSS

    Best way to get the description and populate the table

    1003788
      Hi

      I need a suggestion from you guys to find out the best to way to do my coding.

      I am inserting to a main table from another table. I have all the columns in the main table as same as the other table and I have some additional columns in the main table which are basically description for all the ids in the table.
      I am doing a simple insert..select to load data. In my select statement I added the code for description. Basically the select statement for getting the look up values. Now the number of description columns have increased. It is around 35 description columns. so for each description column I need to write a select in my main insert select statement.

      I would like to know which is the best way to do this. Should I use a loop and calculate the description values separately and do a bulk insert or I need to write the select statement for each individual description
      column ?

      I am using Oracle 11.2 version
       
      INSERT INTO customer 
      (customer_id, 
      extract_dt, 
      provider, 
      id, 
      coif, 
      system_customer_number, 
      system_customer_lead_unionism, 
      repayment_source, 
      debt_service_requirement, 
      relationship_establish_date, 
      borrower_name, 
      flips_code, 
      fips_code_state , 
      fips_code_county , 
      fips_code_region, 
      related_party_loan_code, 
      last_risk_rating_change_date, 
      balance_sheet_date, 
      debt_repayment_coverage_ratio, 
      current_assets, 
      current_liabilities, 
      farm_ops_exp, 
      farm_payment_support, 
      gross_ag_inc, 
      int_exp, 
      non_curr_asset, 
      non_curr_liabilities, 
      net_ag_inc, 
      net_inc, 
      net_worth, 
      nonfarm_inc, 
      income_statement_date, 
      total_assets, 
      total_liabilities, 
      create_id, 
      create_dt, 
      act_strt_dt, 
      act_end_dt, 
      process_flag, 
      receive_dt, 
      ver_num, 
      fcs_surr_cif 
      ) 
      SELECT   dwh_seq.nextval , 
      TO_DATE(extract_dt ,'YYYY-MM-DD') , 
      provider    , 
      uninum  , 
      cif , 
      system_customer_number  , 
      system_customer_lead_uninum , 
      repayment_source    , 
      debt_service_requirement    , 
      TO_DATE(relationship_establish_date,'YYYY-MM-DD') , 
      borrower_name   , 
      fips_code   , 
       (SELECT STATE FROM FCSDWH.FIPS_CODE_LKUP 
      WHERE STATE_ANSI =SUBSTR(fips_code,1,2)) State, 
       (SELECT COUNTY_NAME FROM FCSDWH.FIPS_CODE_LKUP 
      WHERE COUNTY_ANSI =SUBSTR(fips_code,3,3)) County, 
       (SELECT ref_desc FROM FCSDWH.FCS_REF_DATA 
       WHERE REF_DATA_MASTER_ID=(SELECT REF_DATA_MASTER_ID 
                                        FROM FCSDWH.REF_DATA_MASTER 
                                        WHERE DESCRIPTION='REGION') 
                                        AND REF_VALUE=(SELECT STATE FROM FCSDWH.FIPS_CODE_LKUP ---------Like this 35 columns
                                        WHERE STATE_ANSI =SUBSTR(fips_code,1,2))) , 
      related_party_loan_code , 
      TO_DATE(last_risk_rating_change_date,'YYYY-MM-DD')    , 
      TO_DATE(balance_sheet_date ,'YYYY-MM-DD') , 
      debt_repayment_coverage_ratio   , 
      current_assets  , 
      current_liabilities , 
      farm_ops_exp    , 
      farm_payment_support    , 
      gross_ag_inc    , 
      int_exp , 
      non_curr_asset  , 
      non_curr_liabilities    , 
      net_ag_inc  , 
      net_inc , 
      net_worth   , 
      nonfarm_inc , 
      TO_DATE(income_statement_date,'YYYY-MM-DD')   , 
      total_assets    , 
      total_liabilities   , 
      create_id   , 
      create_dt   , 
      act_strt_dt , 
      act_end_dt  , 
      process_flag    , 
      receive_dt  , 
      ver_num , 
      null 
      FROM a_stg 
      AND RECEIVE_DT=P_RECEIVE_DT 
      AND VER_NUM=P_VERSION; 
      Please let me know if you need any more information.
        • 1. Re: Best way to get the description and populate the table
          jeneesh
          I will not prefer PL/SQL for this...

          Multiple OUTER JOINs with the LOOK UP tables is the normal way to do this..

          You will come across with this type of sutuations in DWH developments ... And most of the ETL tools does the OUTER join technique..
          • 2. Re: Best way to get the description and populate the table
            1003788
            Thanks Jeneesh for the quick reply.

            Multiple outer join technique means I have to join look up tables with my main table or I need to write separate select statements for each of the columns like the example I have given?

            Thanks
            Jp
            • 3. Re: Best way to get the description and populate the table
              Frank Kulash
              Hi,
              1000785 wrote:
              Thanks Jeneesh for the quick reply.

              Multiple outer join technique means I have to join look up tables with my main table or I need to write separate select statements for each of the columns like the example I have given?
              Join the look-up tables. Doing a separate query for each look-up item will be much, much slower, and just as
              complicated.

              You say you have 35 columns that have to be looked up. Does that mean you have 35 different tables? If not (that is, if some of the look-ups reference the same table) you might consider unpivoting the data on to multiple rows, doing the lookup with just 1 copy of the dimension table, and then pivoting the results back to a single row. If you'd llikehelp doing this, post a little sample data (CREATE TABLE and INSERT statements) for all tables involved, and the results you want from that sample data.
              Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
              See the forum FAQ {message:id=9360002}
              • 4. Re: Best way to get the description and populate the table
                1003788
                Hi Frank

                Thanks for your time. I posted the insert and create table statements.

                My Oracle version is "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit "
                CREATE TABLE ref_data_master
                    (ref_data_master_id             NUMBER ,
                    description                    VARCHAR2(255 CHAR),
                    create_id                      VARCHAR2(10 CHAR) DEFAULT 'DWH Team',
                    create_dt                      DATE DEFAULT SYSDATE,
                    lst_upd_id                     VARCHAR2(10 CHAR),
                    lst_upd_dt                     DATE DEFAULT SYSDATE,
                    act_strt_dt                    DATE DEFAULT SYSDATE,
                    act_end_dt                     DATE DEFAULT TO_DATE);
                
                CREATE TABLE ref_data
                    (ref_data_id                    NUMBER ,
                    ref_data_master_id             NUMBER ,
                    ref_level                      NUMBER,
                    parent_value                   VARCHAR2(10 CHAR),
                    ref_value                      VARCHAR2(10 CHAR),
                    ref_desc                       VARCHAR2(255 CHAR),
                    is_default                     VARCHAR2(45 CHAR),
                    create_id                      VARCHAR2(10 CHAR) DEFAULT 'DWH Team',
                    create_dt                      DATE DEFAULT SYSDATE,
                    lst_upd_id                     VARCHAR2(10 CHAR),
                    lst_upd_dt                     DATE DEFAULT SYSDATE,
                    act_strt_dt                    DATE DEFAULT SYSDATE,
                    act_end_dt                     DATE DEFAULT TO_DATE);
                
                ----Main table desc. It is a subset of main table  as the table is very big
                
                CREATE TABLE MAIN_TAB (ID NUMBER,
                PROVIDER NUMBER,
                PROVIDER_DESC VARCHAR2(255),
                RELATED_PARTY_LOAN_CODE NUMBER,
                RELATED_PARTY_LOAN_CODE_DESC VARCHAR2(255));
                
                --Staging table desc
                
                CREATE TABLE STAGING_TAB (ID NUMBER,
                PROVIDER NUMBER,
                RELATED_PARTY_LOAN_CODE NUMBER);
                ----------------------------insert for look up master----------------
                
                Insert into  REF_DATA_MASTER (REF_DATA_MASTER_ID,DESCRIPTION,CREATE_ID,CREATE_DT,LST_UPD_ID,LST_UPD_DT,ACT_STRT_DT,ACT_END_DT) values (1,'PROVIDER','DWH Team',to_date('02-APR-13','DD-MON-RR'),null,to_date('02-APR-13','DD-MON-RR'),to_date('02-APR-13','DD-MON-RR'),to_date('01-JAN-00','DD-MON-RR'));
                
                Insert into REF_DATA_MASTER (REF_DATA_MASTER_ID,DESCRIPTION,CREATE_ID,CREATE_DT,LST_UPD_ID,LST_UPD_DT,ACT_STRT_DT,ACT_END_DT) values (2,'RELATED_PARTY_LOAN_CODE','DWH Team',to_date('02-APR-13','DD-MON-RR'),null,to_date('02-APR-13','DD-MON-RR'),to_date('02-APR-13','DD-MON-RR'),to_date('01-JAN-00','DD-MON-RR'));
                
                Insert into REF_DATA_MASTER (REF_DATA_MASTER_ID,DESCRIPTION,CREATE_ID,CREATE_DT,LST_UPD_ID,LST_UPD_DT,ACT_STRT_DT,ACT_END_DT) values (3,'BEGINNING_FARMER_FLAG','DWH Team',to_date('02-APR-13','DD-MON-RR'),null,to_date('02-APR-13','DD-MON-RR'),to_date('02-APR-13','DD-MON-RR'),to_date('01-JAN-00','DD-MON-RR'));
                
                ------------------------Insert for look up detail----------------
                Insert into REF_DATA (REF_DATA_ID,REF_DATA_MASTER_ID,REF_LEVEL,PARENT_VALUE,REF_VALUE,REF_DESC,IS_DEFAULT,CREATE_ID,CREATE_DT,LST_UPD_ID,LST_UPD_DT,ACT_STRT_DT,ACT_END_DT) values (1,1,1,null,'4','PROVIDER1',null,'DWH Team',to_date('02-APR-13','DD-MON-RR'),null,to_date('02-APR-13','DD-MON-RR'),to_date('02-APR-13','DD-MON-RR'),to_date('01-JAN-00','DD-MON-RR'));
                
                Insert into REF_DATA (REF_DATA_ID,REF_DATA_MASTER_ID,REF_LEVEL,PARENT_VALUE,REF_VALUE,REF_DESC,IS_DEFAULT,CREATE_ID,CREATE_DT,LST_UPD_ID,LST_UPD_DT,ACT_STRT_DT,ACT_END_DT) values (2,1,1,null,'5','PROVIDER2',null,'DWH Team',to_date('02-APR-13','DD-MON-RR'),null,to_date('02-APR-13','DD-MON-RR'),to_date('02-APR-13','DD-MON-RR'),to_date('01-JAN-00','DD-MON-RR'));
                
                Insert into REF_DATA (REF_DATA_ID,REF_DATA_MASTER_ID,REF_LEVEL,PARENT_VALUE,REF_VALUE,REF_DESC,IS_DEFAULT,CREATE_ID,CREATE_DT,LST_UPD_ID,LST_UPD_DT,ACT_STRT_DT,ACT_END_DT) values (3,1,1,null,'8','PROVIDER3',null,'DWH Team',to_date('02-APR-13','DD-MON-RR'),null,to_date('02-APR-13','DD-MON-RR'),to_date('02-APR-13','DD-MON-RR'),to_date('01-JAN-00','DD-MON-RR'));
                
                
                Insert into REF_DATA (REF_DATA_ID,REF_DATA_MASTER_ID,REF_LEVEL,PARENT_VALUE,REF_VALUE,REF_DESC,IS_DEFAULT,CREATE_ID,CREATE_DT,LST_UPD_ID,LST_UPD_DT,ACT_STRT_DT,ACT_END_DT) values (14,2,1,null,'1','Director',null,'DWH Team',to_date('02-APR-13','DD-MON-RR'),null,to_date('02-APR-13','DD-MON-RR'),to_date('02-APR-13','DD-MON-RR'),to_date('01-JAN-00','DD-MON-RR'));
                
                Insert into REF_DATA (REF_DATA_ID,REF_DATA_MASTER_ID,REF_LEVEL,PARENT_VALUE,REF_VALUE,REF_DESC,IS_DEFAULT,CREATE_ID,CREATE_DT,LST_UPD_ID,LST_UPD_DT,ACT_STRT_DT,ACT_END_DT) values (15,2,1,null,'2','Employee/Officer',null,'DWH Team',to_date('02-APR-13','DD-MON-RR'),null,to_date('02-APR-13','DD-MON-RR'),to_date('02-APR-13','DD-MON-RR'),to_date('01-JAN-00','DD-MON-RR'));
                
                Insert into REF_DATA (REF_DATA_ID,REF_DATA_MASTER_ID,REF_LEVEL,PARENT_VALUE,REF_VALUE,REF_DESC,IS_DEFAULT,CREATE_ID,CREATE_DT,LST_UPD_ID,LST_UPD_DT,ACT_STRT_DT,ACT_END_DT) values (16,2,1,null,'0','not related or no data',null,'DWH Team',to_date('02-APR-13','DD-MON-RR'),null,to_date('02-APR-13','DD-MON-RR'),to_date('02-APR-13','DD-MON-RR'),to_date('01-JAN-00','DD-MON-RR'));
                
                -----------insert for staging-----------------------
                
                Insert into STAGING_TAB (ID,PROVIDER,RELATED_PARTY_LOAN_CODE) values (1,5,1);
                Insert into STAGING_TAB (ID,PROVIDER,RELATED_PARTY_LOAN_CODE) values (2,8,2);
                Insert into STAGING_TAB (ID,PROVIDER,RELATED_PARTY_LOAN_CODE) values (3,5,1);
                Insert into STAGING_TAB (ID,PROVIDER,RELATED_PARTY_LOAN_CODE) values (3,8,1);
                
                
                
                ------------------insert for main table
                
                Insert into main_tab(ID ,
                PROVIDER ,
                PROVIDER_DESC ),
                RELATED_PARTY_LOAN_CODE ,
                RELATED_PARTY_LOAN_CODE_DESC )
                SELECT PROVIDER,
                (LOGIC FOR PROVIDER_DESC),
                RELATED_PARTY_LOAN_CODE ,
                Logic for RELATED_PARTY_LOAN_CODE_DESC 
                from staging_tab;
                I will match the ref_data_master and ref_data table with ref_data_master_ id and using the description and get the ref_desc
                select ref_desc from the ref_data a, ref_data_master b
                where a. ref_data_master_id=b.ref_data_master_id
                and b.description='PROVIDER'
                and ref_value='Value of  each PROVIDER  from the staging_tab
                I need to find out the description for each of the id columns entered. Most of the description columns are coming from these tables. I wrote the insert select statement as given in the first post. AS there are 35 look up columns, thought of taking your expert opinion for this.

                Thanks in advance
                JP
                • 5. Re: Best way to get the description and populate the table
                  Frank Kulash
                  Hi,

                  Thanks for posting the sample data.
                  Don't forget to post the results you want from that sample data.
                  Given the data in ref_data, ref_data_master and staging_tab, do you want to populate main_tab so that it looks like this?
                  `                                    RELATED
                                               RELATED _PRTY
                                                 _PRTY _LOAN
                                  PROVIDER       _LOAN _CODE
                    ID   PROVIDER _DESC          _CODE _DESC
                  ---- ---------- ---------- --------- --------------------
                     3          5 PROVIDER2          1 Director
                     1          5 PROVIDER2          1 Director
                     2          8 PROVIDER3          2 Employee/Officer
                     3          8 PROVIDER3          1 Director
                  If so, here's one way to do it:
                  INSERT INTO  main_tab ( id
                                              , provider,           provider_desc
                                  , related_party_loan_code, related_party_loan_code_desc
                              --  , ...     -- 33 more pairs
                                  )
                  WITH     got_unique_key     AS
                  (
                       SELECT     id, provider, related_party_loan_code
                       ,     ROW_NUMBER () OVER (ORDER BY NULL)     AS unique_key
                       FROM     staging_tab
                  )
                  ,     unpivoted_data     AS
                  (
                       SELECT     unique_key
                       ,     id
                       ,     label
                       ,     val
                       FROM     got_unique_key
                       UNPIVOT     (    val
                            FOR  label  IN ( provider     AS 'PROVIDER'
                                             , related_party_loan_code     
                                                        AS 'RELATED_PARTY_LOAN_CODE'
                                 --     , ...     -- 33 more columns
                                        )
                            )
                  )
                  ,     got_desc     AS
                  (
                       SELECT     u.unique_key
                       ,     u.id
                       ,     u.label
                       ,     u.val
                  --     ,     m.ref_data_master_id
                       ,     d.ref_desc
                       FROM     unpivoted_data  u
                       JOIN     ref_data_master     m  ON     m.description          = u.label
                       JOIN     ref_data     d  ON     d.ref_data_master_id     = m.ref_data_master_id
                                         AND     d.ref_value          = TO_CHAR (u.val)
                  )
                  SELECT    id
                  ,       provider_val,               provider_dscr
                  ,       related_party_loan_code_val,     related_party_loan_code_dscr
                  FROM       got_desc
                  PIVOT       (     MIN (val)     AS val
                         ,     MIN (ref_desc)     AS dscr
                         FOR     label IN ( 'PROVIDER'     AS provider
                                          , 'RELATED_PARTY_LOAN_CODE'
                                           AS related_party_loan_code
                                --      , ...   -- 33 more columns
                                      )
                         )
                  ;
                  
                  
                  SELECT    *
                  FROM       main_tab
                  ;
                  If staging_tab has a unique key, then this can be made shorter.
                  For each additional column in staging_tab that has to be looked up, you have to add more columns in some of the lists, but you do not have to join any additional tables (or additional copies of the same tables).
                  • 6. Re: Best way to get the description and populate the table
                    1003788
                    Thanks Frank.

                    I am trying to implement this logic. I have not used UNPIVOT yet. Trying to learn that .