Forum Stats

  • 3,759,061 Users
  • 2,251,495 Discussions
  • 7,870,477 Comments

Discussions

Loading data from staging table to main table

Albert Chao
Albert Chao Member Posts: 63 Green Ribbon

----Stage Table


CREATE SEQUENCE DETAILS_STAGING_SQ ;
CREATE TABLE DETAILS_STAGING (
ID NUMBER (10,0) DEFAULT DETAILS_STAGING_SQ.NEXTVAL NOT NULL ENABLE,
KEY_ID NUMBER (10,0) NOT NULL ENABLE,
ID_NUM NUMBER (10,0) NOT NULL ENABLE,
DESCRIPTION VARCHAR2 (255 CHAR) NOT NULL ENABLE,
L7_L8 VARCHAR2 (255 CHAR) NOT NULL ENABLE,
STAGE_0_TO_3 VARCHAR2 (255 CHAR) NOT NULL ENABLE,
STAGE_4_TO_7 VARCHAR2 (255 CHAR) NOT NULL ENABLE,
STAGE_7_TO_10 VARCHAR2 (255 CHAR) NOT NULL ENABLE,
REGION VARCHAR2 (50) NOT NULL ENABLE,
CARD_STATUS VARCHAR2 (50 CHAR) NOT NULL ENABLE,
PROD_LIVE DATE NOT NULL ENABLE,
JIRA_STATUS VARCHAR2 (50 CHAR) NOT NULL ENABLE,
KEY_CONTACT_NAME VARCHAR2 (50 CHAR) NOT NULL ENABLE,
KEY_CONTACT_ID VARCHAR2 (50 CHAR) NOT NULL ENABLE,
EPIC_ID varchar2 (50 char) not null enable,
SUB_TASK_IDS varchar2 (255 char) not null enable,
IS_ACTIVE NUMBER (1,0) NOT NULL ENABLE,
CREATED_BY VARCHAR2 (50 CHAR) NOT NULL ENABLE,
CREATED_ON TIMESTAMP (6) DEFAULT CURRENT TIMESTAMP,
MODIFIED_BY VARCHAR2 (50 CHAR),
MODIFIED_ON TIMESTAMP (6),
CONSTRAINT PK_ELM_JIRA_EXTRACT_DETAILS_STAGING PRIMARY KEY(ID)
);


INSERT INTO DETAILS_STAGING VALUES(1,A11,1009,'GTS','STAT','0. AB TASK','6. CD TASK','9. AD TASK','QUATAR','ACTIVE',TO_DATE('2021/09/30'),'IN DEV');



---Main TABLE


CREATE SEQUENCE DETAILS_STAGING_MAIN_SQ ;
CREATE TABLE DETAILS_STAGING_MAIN (
ID NUMBER (10,0) DEFAULT DETAILS_STAGING_MAIN_SQ.NEXTVAL NOT NULL ENABLE,
KEY_ID NUMBER (10,0) NOT NULL ENABLE,
ID_NUM NUMBER (10,0) NOT NULL ENABLE,
DESCRIPTION VARCHAR2 (255 CHAR) NOT NULL ENABLE,
L7_L8 VARCHAR2 (255 CHAR) NOT NULL ENABLE,
STAGE_CODE VARCHAR2 (255 CHAR) NOT NULL ENABLE,
REGION VARCHAR2 (50) NOT NULL ENABLE,
CARD_STATUS VARCHAR2 (50 CHAR) NOT NULL ENABLE,
PROD_LIVE DATE NOT NULL ENABLE,
JIRA_STATUS_CODE VARCHAR2 (50 CHAR) NOT NULL ENABLE,
KEY_CONTACT_NAME VARCHAR2 (50 CHAR) NOT NULL ENABLE,
KEY_CONTACT_ID VARCHAR2 (50 CHAR) NOT NULL ENABLE,
EPIC_ID varchar2 (50 char) not null enable,
SUB_TASK_IDS varchar2 (255 char) not null enable,
IS_ACTIVE NUMBER (1,0) NOT NULL ENABLE,
CREATED_BY VARCHAR2 (50 CHAR) NOT NULL ENABLE,
CREATED_ON TIMESTAMP (6) DEFAULT CURRENT TIMESTAMP,
MODIFIED_BY VARCHAR2 (50 CHAR),
MODIFIED_ON TIMESTAMP (6),
CONSTRAINT PK_DETAILS_STAGING_MAIN PRIMARY KEY(ID),
CONSTRAINT FK_DETAILS_STAGING_MAIN FOREIGN KEY(STAGE_CODE) REFERENCES (TASK_ID),
CONSTRAINT FK_DETAILS_STAGING_MAIN FOREIGN KEY(JIRA_STATUS_CODE) REFERENCES (STATUS_ID)
);


---Master TABLE----


CREATE SEQUENCE TASK_REF_SQ ;
CREATE TABLE TASK_REF (
TASK_ID NUMBER (10,0) DEFAULT TASK_REF_SQ.NEXTVAL NOT NULL ENABLE,
TASK_STATUS VARCHAR2 (50 CHAR) NOT NULL ENABLE,
COMPLETED_ON DATE NOT NULL ENABLE,
SUMMARY VARCHAR2 (50 CHAR) NOT NULL ENABLE,
IS_ACTIVE NUMBER (1, 0) NOT NULL ENABLE,
CREATED_BY VARCHAR2 (50 CHAR) NOT NULL ENABLE,
CREATED_ON TIMESTAMP (6) DEFAULT CURRENT TIMESTAMP,
MODIFIED_BY VARCHAR2 (50 CHAR),
MODIFIED_ON TIMESTAMP (6),
CONSTRAINT PK_TASK_REF PRIMARY KEY (TASK_ID)
);


INSERT INTO TASK_REF VALUES(1,'0.A TASK');
INSERT INTO TASK_REF VALUES(2,'1.B TASK');
INSERT INTO TASK_REF VALUES(3,'2.C TASK');
INSERT INTO TASK_REF VALUES(4,'3.D TASK');
INSERT INTO TASK_REF VALUES(5,'4.F TASK');
INSERT INTO TASK_REF VALUES(6,'5.CD TASK');
INSERT INTO TASK_REF VALUES(7,'6.G TASK');
INSERT INTO TASK_REF VALUES(8,'7.H TASK');
INSERT INTO TASK_REF VALUES(9,'8.I TASK');
INSERT INTO TASK_REF VALUES(10,'9.AD TASK');
INSERT INTO TASK_REF VALUES(11,'10.J TASK');




CREATE SEQUENCE JIRA_STATUS_REF_SQ;
CREATE TABLE JIRA_STATUS_REF
STATUS_ID NUMBER (10,0) DEFAULT ELM_JIRA_STATUS_REF_SQ.NEXTVAL NOT NULL ENABLE,
JIRA_STATUS VARCHAR2 (50 CHAR) NOT NULL ENABLE,
IS ACTIVE NUMBER (1,0) NOT NULL ENABLE,
CREATED BY VARCHAR2 (50 CHAR) NOT NULL ENABLE,
CREATED_ON TIMESTAMP (6) DEFAULT CURRENT TIMESTAMP,
MODIFIED BY VARCHAR2 (50 CHAR),
MODIFIED ON TIMESTAMP (6)
CONSTRAINT PK_JIRA_STATUS_REF PRIMARY KEY (STATUS_ID)
);


INSERT INTO JIRA_STATUS_REF VALUES(1,'IN DEV');
INSERT INTO JIRA_STATUS_REF VALUES(2,'NOT STARTED');
INSERT INTO JIRA_STATUS_REF VALUES(3,'STARTED');




REQUIREMENT ------------


-- I want to create one stored procedure to load the data from stage table DETAILS_STAGING to main table DETAILS_STAGING_MAIN

  for common columns and getting the master data values.

  

-- When the SP is called first validaton will be done and next data load will be done from DETAILS_STAGING to DETAILS_STAGING_MAIN for only valid records.


--To get the STAGE_CODE and JIRA_STATUS_CODE of main table DETAILS_STAGING_MAIN. 

We will select NVL(NVL(STAGE_0_TO_3, STAGE_4_TO_7 , STAGE_7_TO_10) from DETAILS_STAGING. This will give value in single column..then the value has to be joined with

master table TASK_REF and fetch the STAGE_CODE from master table TASK_REF.

Same have to do with JIRA_STATUS_CODE from JIRA_STATUS_REF.

Tagged:

Best Answer

  • JonWat
    JonWat Member Posts: 536 Silver Badge
    Accepted Answer

    Hi,

    Looking at your procedure, it looks like you haven't really understood that SQL statements work on a SET of rows, not a single row. Your procedure might be more-or-less OK if it was fed a single row from DETAILS_STAGING. but that would a) require some structure (like a CURSOR FOR loop) to pass one row at a time, and b) would be just about the slowest way of processing the data.

    One way of doing what you are trying to do would be to put a filter on the rows you try to insert, e.g.

    insert into DETAILS_STAGING_MAIN (KEY_ID, ID_NUM, DESCRIPTION,
    L7_L8, REGION, CARD_STATUS, PROD_LIVE, KEY_CONTACT_NAME, KEY_CONTACT_ID, EPIC_ID, SUB_TASK_IDS)
    select KEY_ID, ID_NUM, DESCRIPTION,
    L7_L8, REGION, EUC_STATUS, PROD_LIVE, KEY_CONTACT_NAME, KEY_CONTACT_ID, EPIC_ID, SUB_TASK_IDS from
    DETAILS_STAGING
    WHERE key_id is not null and id_num is not null;
    

    You can count the records you are going to reject all at the same time, like:

    select count(*) into reject_count
    from details_staging 
    where key_id is null or id_num is null;
    

    Also, in that first bracket in the query you have the columns which are the destination for the data from the select. You should not have a call to the nextval the SEQ there. Just let the DEFAULT do its job.

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond

    Thanks for posting example data.

    So, what have you tried and what's not working for you?

  • Albert Chao
    Albert Chao Member Posts: 63 Green Ribbon

    @BluShadow Tried below code

    create or replace PROCEDURE SP_EXTRACT
    ov_error_msg OUT VARCHAR
    )
    AS
    lv count number (10);
    lv rej count number (10);
    lv_threshold rej_count number (10) := 100;
    BEGIN
    select KEY_ID into lv_count from DETAILS_STAGING;
    IF lv count IS NULL THEN
    ov_error_msg :='Key ID not found' ;
    lv_rej_count := lv_rej_count +1 ;
    end if;
    select ID_NUM into lv count from DETAILS STAGING;
    IF Iv_count IS NULL THEN
    ov_error msg :='EUC ID not found';
    lv_rej_count := lv rej_count +1 ;
    end if;
    select KEY_ID into lv count from DETAILS STAGING;
    IF lv_count IS NOT NULL THEN
    insert into DETAILS STAGING MAIN (DETAILS STAGING MAIN.nextval, KEY_ID, ID_NUM, DESCRIPTION,
    L7_L8, REGION, CARD_STATUS, PROD_LIVE, KEY_CONTACT_NAME, KEY_CONTACT_ID, EPIC_ID, SUB_TASK_IDS)
    select KEY_ID, ID_NUM, DESCRIPTION,
    L7_L8, REGION, EUC_STATUS, PROD_LIVE, KEY_CONTACT_NAME, KEY_CONTACT_ID, EPIC_ID, SUB_TASK_IDS from
    DETAILS_STAGING;
    end if;
    
    END SP_EXTRACT;
    


  • Albert Chao
    Albert Chao Member Posts: 63 Green Ribbon

    @BluShadow

    Tried below code

    create or replace PROCEDURE SP_EXTRACT
    ov_error_msg OUT VARCHAR
    )
    AS
    lv count number (10);
    lv rej count number (10);
    lv_threshold rej_count number (10) := 100;
    BEGIN
    select KEY_ID into lv_count from DETAILS_STAGING;
    IF lv count IS NULL THEN
    ov_error_msg :='Key ID not found' ;
    lv_rej_count := lv_rej_count +1 ;
    end if;
    select ID_NUM into lv count from DETAILS STAGING;
    IF Iv_count IS NULL THEN
    ov_error msg :='EUC ID not found';
    lv_rej_count := lv rej_count +1 ;
    end if;
    select KEY_ID into lv count from DETAILS STAGING;
    IF lv_count IS NOT NULL THEN
    insert into DETAILS STAGING MAIN (DETAILS STAGING MAIN.nextval, KEY_ID, ID_NUM, DESCRIPTION,
    L7_L8, REGION, CARD_STATUS, PROD_LIVE, KEY_CONTACT_NAME, KEY_CONTACT_ID, EPIC_ID, SUB_TASK_IDS)
    select KEY_ID, ID_NUM, DESCRIPTION,
    L7_L8, REGION, EUC_STATUS, PROD_LIVE, KEY_CONTACT_NAME, KEY_CONTACT_ID, EPIC_ID, SUB_TASK_IDS from
    DETAILS_STAGING;
    end if;
    
    END SP_EXTRACT;
    
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,060 Red Diamond

    Hi, @Albert Chao

    Whenever you have a question, always post the results you want from the given sample data. It's great to describe the results, but describe them in addition to (not instead of) actually posting them. In the case of a DML question (such as INSERT) the desired results are the contents of the changed table after the DML is finished.

    Do you want answers that work? Make sure the CREATE TABLE and INSERT statmements you post work, too. Test (and, if necessary, fix) your statements before you post them.

    It looks like you're missing some left ('s, and that you have spaces where underscores would make sense. for example, instead of :

    create or replace PROCEDURE SP_EXTRACT
    ov_error_msg OUT VARCHAR
    )
    AS
    lv count number (10);
    lv rej count number (10);
    lv_threshold rej_count number (10) := 100;
    ...
    

    perhaps you meant:

    CREATE OR REPLACE PROCEDURE sp_extract
    (
        ov_error_msg OUT VARCHAR2
    )
    AS
        lv_count			NUMBER (10);
        lv_rej_count    		NUMBER (10);
        lv_threshold_rej_count 	NUMBER (10) := 100;
    ...
    

    Always format your code to make it easy for people (including yourself) to read and debug it.

    Albert Chao
  • EdStevens
    EdStevens Member Posts: 28,467 Gold Crown
  • Paulzip
    Paulzip Member Posts: 8,452 Blue Diamond

    Just use MViews with an MView log on the master table to give you fast refresh capabilities.

    Your logic can be easily incorporated into the MViews query. It looks fairly trivial to me.

    Then all you do is refresh the staging MView - which can be built on your existing staging tables (called a prebuilt table in MView terminology).

  • JonWat
    JonWat Member Posts: 536 Silver Badge
    Accepted Answer

    Hi,

    Looking at your procedure, it looks like you haven't really understood that SQL statements work on a SET of rows, not a single row. Your procedure might be more-or-less OK if it was fed a single row from DETAILS_STAGING. but that would a) require some structure (like a CURSOR FOR loop) to pass one row at a time, and b) would be just about the slowest way of processing the data.

    One way of doing what you are trying to do would be to put a filter on the rows you try to insert, e.g.

    insert into DETAILS_STAGING_MAIN (KEY_ID, ID_NUM, DESCRIPTION,
    L7_L8, REGION, CARD_STATUS, PROD_LIVE, KEY_CONTACT_NAME, KEY_CONTACT_ID, EPIC_ID, SUB_TASK_IDS)
    select KEY_ID, ID_NUM, DESCRIPTION,
    L7_L8, REGION, EUC_STATUS, PROD_LIVE, KEY_CONTACT_NAME, KEY_CONTACT_ID, EPIC_ID, SUB_TASK_IDS from
    DETAILS_STAGING
    WHERE key_id is not null and id_num is not null;
    

    You can count the records you are going to reject all at the same time, like:

    select count(*) into reject_count
    from details_staging 
    where key_id is null or id_num is null;
    

    Also, in that first bracket in the query you have the columns which are the destination for the data from the select. You should not have a call to the nextval the SEQ there. Just let the DEFAULT do its job.