1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Need Help in Teradata To PLSQL Procedure COnversions

Discussion in 'General' started by baluaggala, Dec 13, 2014.

  1. baluaggala

    baluaggala Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    1) In Teradata :
    DECLARE errorstate condition for sqlstate '42000';

    L1: BEGIN

    SET SQLERRCDE = 0;
    SET SQLSTTE = 0;

    -- DELETE RECORDS FROM THE ERROR LOG TABLE IN CASE OF RERUN

    SET TMPMSG = 'DELETE FROM ICDW_SPROC_T.SPROC_ERR_LOG...';

    DELETE FROM ICDW_SPROC_T.SPROC_ERR_LOG
    WHERE db_nm = 'ICDW_SPROC'
    AND sproc_nm = 'SPROC_GEN_KEY~'||:tgttblnm
    AND cre_run_id = :crerunid ;



    -- CHECK VALID SRC_SYS_CD

    IF srcsyscd = '' THEN
    SET tmpMsg = 'APP ERROR: Invalid Parameter - SRC_SYS_CD is Required!';
    SET SQLMSG = TMPMSG;
    SET SQLERRCDE = 99;
    SET SQLSTTE = 99;
    SET SQL_ERR_CDE = 99;
    SET SQL_STATE = 99;
    INSERT INTO ICDW_SPROC_T.SPROC_ERR_LOG
    VALUES ('ICDW_SPROC','SPROC_GEN_KEY~'||:tgttblnm,'E',:SQL_ERR_CDE,:SQL_STATE,:TMPMSG,:crerunid,CURRENT_TIMESTAMP(0));
    SIGNAL ERRORSTATE;
    LEAVE L1;
    END IF;


    In oracle How to handle the above
    DECLARE errorstate condition for sqlstate '42000';

    L1: BEGIN

    SIGNAL ERRORSTATE;
    LEAVE L1;


    ===================================================================>>>>


    In Teradata :

    LABEL_CHK_CDC_LST:
    WHILE INDEX_CDC <> 0 DO

    SET INDEX_CDC = INDEX(CDCSTMT,',');
    IF(INDEX_CDC>0) THEN
    SET CDC_COL = TRIM(SUBSTR(CDCSTMT,1,INDEX_CDC-1));
    SET CDCSTMT = SUBSTR(CDCSTMT,INDEX_CDC+1);
    ELSE
    SET CDC_COL = TRIM(CDCSTMT);
    END IF;

    SEL
    COUNT(*) INTO :IS_CDC_COL
    FROM
    DBC.COLUMNS A
    WHERE A.DATABASENAME = :TGTDBNM
    AND A.TABLENAME = :TGTTBLNM
    AND A.COLUMNNAME = :CDC_COL;

    --FAIL THE PROCESS IF COLUMNS IN PARTIAL CDC LIST ARE NOT MATCHING DBC.COLUMNS
    IF IS_CDC_COL = '0' THEN
    SET TMPMSG = 'APP ERROR: PARTIAL CDC COLUMN LIST IN SPROC_CDC_LOAD_CNTL IS NOT VALID FOR ' || TGTDBNM || '.' || TGTTBLNM || ' !';
    SET SQLMSG = TMPMSG;
    SET SQL_ERR_CDE = '99';
    SET SQL_STATE = '99';
    INSERT INTO ICDW_SPROC_T.SPROC_ERR_LOG
    VALUES ('ICDW_SPROC','SPROC_CDC_TGT~'||:TGTTBLNM,'E',:SQL_ERR_CDE,:SQL_STATE,:TMPMSG,:CRERUNID,CURRENT_TIMESTAMP(0));
    SIGNAL ERRORSTATE;
    LEAVE L1;
    END IF;
    ITERATE LABEL_CHK_CDC_LST;
    END WHILE LABEL_CHK_CDC_LST;


    In Oracle How to handle this Label Loop concept ?? please help me out