Not really sure if i am using it for the intended purpose, but who cares :)
I will try adding a few technical info for my own reference later on.
The following lists all the dates within the next 10 yrs, starting from current date.
I keep forgetting the all_objects table in Oracle. So, this is for my reference.
select TRUNC((sysdate) +rownum -1)
from all_objects
where rownum <= 4018
The below procedure in Teradata does the work on a Persistant staging area load. Even though it is quite generic, the syntax and symantics are quite helpful.
REPLACE PROCEDURE SP_GENERIC_PSA
(
IN TABLE_NAME VARCHAR(50)
)
BEGIN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Generic Declarations and variables
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE v_columns varchar(50);
DECLARE v_sql varchar(5000) DEFAULT ' ';
DECLARE v_vw_sql varchar(5000) DEFAULT ' ';
DECLARE v_INSERT2_sql varchar(5000) DEFAULT ' ';
DECLARE v_view_cols varchar(5000) DEFAULT ' ';
DECLARE v_INSERT1_sql varchar(5000) DEFAULT ' ';
DECLARE v_UPDATE_sql varchar(5000) DEFAULT ' ';
DECLARE v_DELETE_sql varchar(5000) DEFAULT ' ';
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Needed for Actual Error Control - Can be used later
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*DECLARE v_set integer;
DECLARE v_analyze_flag integer;
DECLARE v_step integer;
DECLARE v_sql_error varchar(255);
DECLARE v_sql_code integer;*/
DECLARE v_count integer;
DECLARE v_status integer;
DECLARE v_tab_count integer;
DECLARE v_col_count integer;
DECLARE v_vw_indctr integer;
DECLARE pky_COLUMN1 varchar(50);
DECLARE pky_COLUMN2 varchar(50);
DECLARE pky_COLUMN3 varchar(50);
DECLARE pky_COLUMN4 varchar(50);
DECLARE pky_COLUMN5 varchar(50);
DECLARE VIEW_NAME varchar(50);
DECLARE TABLE_NAME_STG varchar(50);
DECLARE v_log_count_sql varchar(5000) DEFAULT ' ';
DECLARE v_FLAG_Y_sql varchar(5000) DEFAULT ' ';
DECLARE v_FLAG_N_sql varchar(5000) DEFAULT ' ';
SET VIEW_NAME = 'VW_'||TABLE_NAME;
SET TABLE_NAME_STG = TABLE_NAME||'_STG';
----------------------------------------
-- Cursors----------
--------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--- Default---
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SET v_count = 0;
SET v_tab_count = 0;
SET v_col_count = 0;
--- Validate the existance of the table.
Select count(*) into v_tab_count
from dbc.tablesV
where TRIM(TableName) = :TABLE_NAME;
--- Validate the existance of the view. The dynamic sql will create of replace the view based on the result
Select count(*) into v_vw_indctr
from dbc.tablesV
where TRIM(TableName) = :VIEW_NAME;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- This block is used to get all the Primary Keys for a given table and store them individually in variables..
-- Upto 5 Primary Keys( excluding Rec_start_date) are supported.. Can be expanded.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IF v_tab_count >= 1 THEN
FOR cur_pky AS psapky
CURSOR FOR
SELECT COLUMNNAME
from dbc.IndicesV
where TableName =:TABLE_NAME
AND ColumnName <> 'REC_START_DATE'
AND INDEXTYPE ='K'
DO
SET v_count = v_count+1;
CASE v_Count
WHEN 1 THEN
SET pky_COLUMN1 = cur_pky.COLUMNNAME;
WHEN 2 THEN
SET pky_COLUMN2 = cur_pky.COLUMNNAME;
WHEN 3 THEN
SET pky_COLUMN3 = cur_pky.COLUMNNAME;
WHEN 4 THEN
SET pky_COLUMN4 = cur_pky.COLUMNNAME;
WHEN 5 THEN
SET pky_COLUMN5 = cur_pky.COLUMNNAME;
ELSE
INSERT into TBL_LOG_DATA
VALUES (current_timestamp,:v_count);
END CASE ;
END FOR ;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Get a list of all columns from Stage table to create a view dynamically and also to use it iin the dynamic sql for insert.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
FOR cur_columns AS psacolumns
CURSOR FOR
Select COLUMNNAME
from dbc.ColumnsV
where TableName = :TABLE_NAME_STG
order by columnid
DO
SET v_columns = cur_columns.COLUMNNAME;
SET v_sql = v_sql || v_columns||',';
END FOR ;
SET v_view_cols = TRIM(TRAILING ',' FROM v_sql);
IF v_vw_indctr = 0 THEN
SET v_vw_sql = 'CREATE VIEW VW_'||TABLE_NAME||' AS SELECT ' ||v_view_cols|| ' FROM '||TABLE_NAME||' WHERE CURR_REC_INDCTR in (''Y'',
''I'' ,''V'');';
CALL DBC.SYSEXECSQL(v_vw_sql);
ELSE
SET v_vw_sql = 'REPLACE VIEW VW_'||TABLE_NAME||' AS SELECT ' ||v_view_cols|| ' FROM '||TABLE_NAME||' WHERE CURR_REC_INDCTR in (''Y'',
''I'' ,''V'');';
CALL DBC.SYSEXECSQL(v_vw_sql);
END IF ;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- The actual PSA logic is contained in here. Note that the case statement is used to implement the same
-- logic for upto 5 Primary keys in the PSA table (Excluding Rec_start_Date).
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CASE v_count
WHEN 1 THEN
SET v_DELETE_sql = 'UPDATE '||TABLE_NAME||' SET CURR_REC_INDCTR =''D'' , REC_START_DATE = current_timestamp where ' ||pky_COLUMN1|| ' in (Select '||pky_COLUMN1||' from (Select * from '||VIEW_NAME|| ' minus Select * from '||TABLE_NAME_STG|| ' ) DUMMY ) ' ;
CALL DBC.SYSEXECSQL(v_DELETE_sql);
SET v_UPDATE_sql = 'UPDATE '|| TABLE_NAME || ' SET CURR_REC_INDCTR = ''V'', REC_START_DATE = current_timestamp where ' ||pky_COLUMN1|| ' in (Select '||pky_COLUMN1||' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY ) ' ;
CALL DBC.SYSEXECSQL(v_UPDATE_sql);
SET v_INSERT2_sql= 'INSERT INTO ' || TABLE_NAME || ' Select current_timestamp, ' ||v_sql|| ' current_timestamp,current_timestamp,2,''U'' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY where '||pky_COLUMN1||' IN ( Select distinct '||pky_COLUMN1||' from '||VIEW_NAME||' )' ;
SET v_INSERT1_sql= 'INSERT INTO ' || TABLE_NAME || ' Select current_timestamp, ' ||v_sql|| ' current_timestamp,current_timestamp,1,''I'' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY where '||pky_COLUMN1||' NOT IN ( Select distinct '||pky_COLUMN1||' from '||VIEW_NAME||' ) ';
CALL DBC.SYSEXECSQL(v_INSERT2_sql);
CALL DBC.SYSEXECSQL(v_INSERT1_sql);
INSERT into TBL_LOG_DATA
VALUES (current_timestamp,:v_INSERT1_sql);
WHEN 2 THEN
SET v_DELETE_sql ='UPDATE '|| TABLE_NAME || ' SET CURR_REC_INDCTR = ''D'' , REC_START_DATE = current_timestamp where (' ||pky_COLUMN1|| ', '||pky_COLUMN2||') in (Select ' ||pky_COLUMN1|| ','||pky_COLUMN2||' from (Select * from '||VIEW_NAME|| ' minus Select * from '||TABLE_NAME_STG|| ' ) DUMMY ) ' ;
CALL DBC.SYSEXECSQL(v_DELETE_sql);
SET v_UPDATE_sql = 'UPDATE '|| TABLE_NAME || ' SET CURR_REC_INDCTR = ''V'' , REC_START_DATE = current_timestamp where (' ||pky_COLUMN1|| ', '||pky_COLUMN2||') in (Select ' ||pky_COLUMN1|| ','||pky_COLUMN2||' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY ) ' ;
CALL DBC.SYSEXECSQL(v_UPDATE_sql);
SET v_INSERT2_sql= 'INSERT INTO ' || TABLE_NAME || ' Select current_timestamp, ' ||v_sql|| ' current_timestamp,current_timestamp,2,''U'' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY where (' ||pky_COLUMN1|| ','||pky_COLUMN2||') IN ( Select distinct ' ||pky_COLUMN1|| ','||pky_COLUMN2||' from '||VIEW_NAME||' )' ;
SET v_INSERT1_sql= 'INSERT INTO ' || TABLE_NAME || ' Select current_timestamp, ' ||v_sql|| ' current_timestamp,current_timestamp,1,''I'' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY where (' ||pky_COLUMN1|| ','||pky_COLUMN2||') NOT IN ( Select distinct ' ||pky_COLUMN1|| ','||pky_COLUMN2||' from '||VIEW_NAME||' )' ;
CALL DBC.SYSEXECSQL(v_INSERT2_sql);
CALL DBC.SYSEXECSQL(v_INSERT1_sql);
WHEN 3 THEN
SET v_DELETE_sql ='UPDATE '|| TABLE_NAME || ' SET CURR_REC_INDCTR = ''D'' , REC_START_DATE = current_timestamp where (' ||pky_COLUMN1|| ', ' ||pky_COLUMN2|| ','||pky_COLUMN3||') in (Select '||pky_COLUMN1|| ', ' ||pky_COLUMN2|| ','||pky_COLUMN3||' (Select * from '||VIEW_NAME|| ' minus Select * from '||TABLE_NAME_STG|| ' ) DUMMY ) ' ;
CALL DBC.SYSEXECSQL(v_DELETE_sql);
SET v_UPDATE_sql = 'UPDATE '|| TABLE_NAME || ' SET CURR_REC_INDCTR = ''D'' , REC_START_DATE = current_timestamp where (' ||pky_COLUMN1|| ', ' ||pky_COLUMN2|| ','||pky_COLUMN3||') in (Select '||pky_COLUMN1|| ', ' ||pky_COLUMN2|| ','||pky_COLUMN3||' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY ) ' ;
CALL DBC.SYSEXECSQL(v_UPDATE_sql);
SET v_INSERT2_sql= 'INSERT INTO ' || TABLE_NAME || ' Select current_timestamp, ' ||v_sql|| ' current_timestamp,current_timestamp,2,''U'' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY where (' ||pky_COLUMN1|| ', ' ||pky_COLUMN2|| ','||pky_COLUMN3||') IN ( Select distinct ' ||pky_COLUMN1|| ', ' ||pky_COLUMN2|| ','||pky_COLUMN3|| ' from '||VIEW_NAME||' )' ;
SET v_INSERT1_sql= 'INSERT INTO ' || TABLE_NAME || ' Select current_timestamp, ' ||v_sql|| ' current_timestamp,current_timestamp,1,''I'' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY where (' ||pky_COLUMN1|| ', ' ||pky_COLUMN2|| ','||pky_COLUMN3||') NOT IN ( Select distinct ' ||pky_COLUMN1|| ', ' ||pky_COLUMN2|| ','||pky_COLUMN3||' from '||VIEW_NAME||' )' ;
CALL DBC.SYSEXECSQL(v_INSERT2_sql);
CALL DBC.SYSEXECSQL(v_INSERT1_sql);
WHEN 4 THEN
SET v_DELETE_sql = 'UPDATE '|| TABLE_NAME || ' SET CURR_REC_INDCTR = ''D'' , REC_START_DATE = current_timestamp where (' ||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||') in (Select ' ||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||' from (Select * from '||VIEW_NAME|| ' minus Select * from '||TABLE_NAME_STG|| ' ) DUMMY ) ' ;
CALL DBC.SYSEXECSQL(v_DELETE_sql);
SET v_UPDATE_sql = 'UPDATE '|| TABLE_NAME || ' SET CURR_REC_INDCTR = ''D'' , REC_START_DATE = current_timestamp where (' ||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||') in (Select ' ||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY ) ' ;
CALL DBC.SYSEXECSQL(v_UPDATE_sql);
SET v_INSERT2_sql= 'INSERT INTO ' || TABLE_NAME || ' Select current_timestamp, ' ||v_sql|| ' current_timestamp,current_timestamp,2,''U'' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY where (' ||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||') IN ( Select distinct ' ||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||' from '||VIEW_NAME||' )' ;
SET v_INSERT1_sql= 'INSERT INTO ' || TABLE_NAME || ' Select current_timestamp, ' ||v_sql|| ' current_timestamp,current_timestamp,1,''I'' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY where (' ||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||') NOT IN ( Select distinct '||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||' from '||VIEW_NAME||' )' ;
CALL DBC.SYSEXECSQL(v_INSERT2_sql);
CALL DBC.SYSEXECSQL(v_INSERT1_sql);
WHEN 5 THEN
SET v_DELETE_sql ='UPDATE '|| TABLE_NAME || ' SET CURR_REC_INDCTR = ''D'' , REC_START_DATE = current_timestamp where (' ||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||',' ||pky_COLUMN5|| ') in (Select '||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||',' ||pky_COLUMN5|| ' from (Select * from '||VIEW_NAME|| ' minus Select * from '||TABLE_NAME_STG|| ' ) DUMMY ) ' ;
CALL DBC.SYSEXECSQL(v_DELETE_sql);
SET v_UPDATE_sql = 'UPDATE '|| TABLE_NAME || ' SET CURR_REC_INDCTR = ''D'' , REC_START_DATE = current_timestamp where (' ||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||',' ||pky_COLUMN5|| ') in (Select '||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||',' ||pky_COLUMN5|| ' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY ) ' ;
CALL DBC.SYSEXECSQL(v_UPDATE_sql);
SET v_INSERT2_sql= 'INSERT INTO ' || TABLE_NAME || ' Select current_timestamp, ' ||v_sql|| ' current_timestamp,current_timestamp,2,''Y'' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY where (' ||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||',' ||pky_COLUMN5|| ') IN ( Select distinct ' ||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||',' ||pky_COLUMN5|| ' from '||VIEW_NAME||' )' ;
SET v_INSERT1_sql= 'INSERT INTO ' || TABLE_NAME || ' Select current_timestamp, ' ||v_sql|| ' current_timestamp,current_timestamp,1,''I'' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY where (' ||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||',' ||pky_COLUMN5|| ') NOT IN ( Select distinct ' ||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||',' ||pky_COLUMN5|| ' from '||VIEW_NAME||' )' ;
CALL DBC.SYSEXECSQL(v_INSERT2_sql);
CALL DBC.SYSEXECSQL(v_INSERT1_sql);
ELSE
INSERT into TBL_LOG_DATA
VALUES (current_timestamp,'Too Many Keys.. Time to add another case block');
END CASE ;
ELSEIF v_tab_count=0
or v_col_count=0 THEN
INSERT into TBL_LOG_DATA
VALUES (current_timestamp,'Problem with the load');
END IF ;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Audit
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- The below query returns Audit records, only when anything changes.
/*SET v_log_count_sql = 'INSERT INTO TBL_PSA_LOAD_STATISTICS( REC_LOAD_DATE,PSA_TABLE_NAME,LOAD_TYPE,REC_COUNT) Select current_timestamp,'''||TABLE_NAME||''' , CURR_REC_INDCTR, count(*) from '||TABLE_NAME||' where CURR_REC_INDCTR in (''I'',''D'',''U'')
GROUP by current_timestamp,'''||TABLE_NAME||''' , CURR_REC_INDCTR';
*/
-- The below query returns a zero record if there are no changes.
SET v_log_count_sql = 'INSERT INTO TBL_PSA_LOAD_STATISTICS( REC_LOAD_DATE,PSA_TABLE_NAME,LOAD_TYPE,REC_COUNT) Select current_timestamp,'''||TABLE_NAME||''' ,
CURR_REC_INDCTR, SUM(REC_COUNT) from (Select CURR_REC_INDCTR , count(*) REC_COUNT from '||TABLE_NAME||' where CURR_REC_INDCTR in (''I'',''D'',''U'')
GROUP by CURR_REC_INDCTR union
Select ''I'' CURR_REC_INDCTR, 0 REC_COUNT from sys_calendar.caldates where cdate =date
union
Select ''D'' CURR_REC_INDCTR, 0 REC_COUNT from sys_calendar.caldates where cdate =date
union
Select ''U'' CURR_REC_INDCTR, 0 REC_COUNT from sys_calendar.caldates where cdate =date) AUDIT
GROUP BY CURR_REC_INDCTR';
CALL DBC.SYSEXECSQL(v_log_count_sql);
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Set the indicators to 'Y' and 'N'
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SET v_FLAG_Y_sql = 'UPDATE ' ||TABLE_NAME||' SET CURR_REC_INDCTR = ''Y'' where CURR_REC_INDCTR in (''I'',''U'')';
CALL DBC.SYSEXECSQL(v_FLAG_Y_sql );
SET v_FLAG_N_sql = 'UPDATE ' ||TABLE_NAME||' SET CURR_REC_INDCTR =''N'' where CURR_REC_INDCTR in (''V'',''D'')';
CALL DBC.SYSEXECSQL(v_FLAG_N_sql );
END ;
I will try adding a few technical info for my own reference later on.
The following lists all the dates within the next 10 yrs, starting from current date.
I keep forgetting the all_objects table in Oracle. So, this is for my reference.
select TRUNC((sysdate) +rownum -1)
from all_objects
where rownum <= 4018
The below procedure in Teradata does the work on a Persistant staging area load. Even though it is quite generic, the syntax and symantics are quite helpful.
REPLACE PROCEDURE SP_GENERIC_PSA
(
IN TABLE_NAME VARCHAR(50)
)
BEGIN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Generic Declarations and variables
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE v_columns varchar(50);
DECLARE v_sql varchar(5000) DEFAULT ' ';
DECLARE v_vw_sql varchar(5000) DEFAULT ' ';
DECLARE v_INSERT2_sql varchar(5000) DEFAULT ' ';
DECLARE v_view_cols varchar(5000) DEFAULT ' ';
DECLARE v_INSERT1_sql varchar(5000) DEFAULT ' ';
DECLARE v_UPDATE_sql varchar(5000) DEFAULT ' ';
DECLARE v_DELETE_sql varchar(5000) DEFAULT ' ';
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Needed for Actual Error Control - Can be used later
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*DECLARE v_set integer;
DECLARE v_analyze_flag integer;
DECLARE v_step integer;
DECLARE v_sql_error varchar(255);
DECLARE v_sql_code integer;*/
DECLARE v_count integer;
DECLARE v_status integer;
DECLARE v_tab_count integer;
DECLARE v_col_count integer;
DECLARE v_vw_indctr integer;
DECLARE pky_COLUMN1 varchar(50);
DECLARE pky_COLUMN2 varchar(50);
DECLARE pky_COLUMN3 varchar(50);
DECLARE pky_COLUMN4 varchar(50);
DECLARE pky_COLUMN5 varchar(50);
DECLARE VIEW_NAME varchar(50);
DECLARE TABLE_NAME_STG varchar(50);
DECLARE v_log_count_sql varchar(5000) DEFAULT ' ';
DECLARE v_FLAG_Y_sql varchar(5000) DEFAULT ' ';
DECLARE v_FLAG_N_sql varchar(5000) DEFAULT ' ';
SET VIEW_NAME = 'VW_'||TABLE_NAME;
SET TABLE_NAME_STG = TABLE_NAME||'_STG';
----------------------------------------
-- Cursors----------
--------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--- Default---
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SET v_count = 0;
SET v_tab_count = 0;
SET v_col_count = 0;
--- Validate the existance of the table.
Select count(*) into v_tab_count
from dbc.tablesV
where TRIM(TableName) = :TABLE_NAME;
--- Validate the existance of the view. The dynamic sql will create of replace the view based on the result
Select count(*) into v_vw_indctr
from dbc.tablesV
where TRIM(TableName) = :VIEW_NAME;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- This block is used to get all the Primary Keys for a given table and store them individually in variables..
-- Upto 5 Primary Keys( excluding Rec_start_date) are supported.. Can be expanded.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IF v_tab_count >= 1 THEN
FOR cur_pky AS psapky
CURSOR FOR
SELECT COLUMNNAME
from dbc.IndicesV
where TableName =:TABLE_NAME
AND ColumnName <> 'REC_START_DATE'
AND INDEXTYPE ='K'
DO
SET v_count = v_count+1;
CASE v_Count
WHEN 1 THEN
SET pky_COLUMN1 = cur_pky.COLUMNNAME;
WHEN 2 THEN
SET pky_COLUMN2 = cur_pky.COLUMNNAME;
WHEN 3 THEN
SET pky_COLUMN3 = cur_pky.COLUMNNAME;
WHEN 4 THEN
SET pky_COLUMN4 = cur_pky.COLUMNNAME;
WHEN 5 THEN
SET pky_COLUMN5 = cur_pky.COLUMNNAME;
ELSE
INSERT into TBL_LOG_DATA
VALUES (current_timestamp,:v_count);
END CASE ;
END FOR ;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Get a list of all columns from Stage table to create a view dynamically and also to use it iin the dynamic sql for insert.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
FOR cur_columns AS psacolumns
CURSOR FOR
Select COLUMNNAME
from dbc.ColumnsV
where TableName = :TABLE_NAME_STG
order by columnid
DO
SET v_columns = cur_columns.COLUMNNAME;
SET v_sql = v_sql || v_columns||',';
END FOR ;
SET v_view_cols = TRIM(TRAILING ',' FROM v_sql);
IF v_vw_indctr = 0 THEN
SET v_vw_sql = 'CREATE VIEW VW_'||TABLE_NAME||' AS SELECT ' ||v_view_cols|| ' FROM '||TABLE_NAME||' WHERE CURR_REC_INDCTR in (''Y'',
''I'' ,''V'');';
CALL DBC.SYSEXECSQL(v_vw_sql);
ELSE
SET v_vw_sql = 'REPLACE VIEW VW_'||TABLE_NAME||' AS SELECT ' ||v_view_cols|| ' FROM '||TABLE_NAME||' WHERE CURR_REC_INDCTR in (''Y'',
''I'' ,''V'');';
CALL DBC.SYSEXECSQL(v_vw_sql);
END IF ;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- The actual PSA logic is contained in here. Note that the case statement is used to implement the same
-- logic for upto 5 Primary keys in the PSA table (Excluding Rec_start_Date).
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CASE v_count
WHEN 1 THEN
SET v_DELETE_sql = 'UPDATE '||TABLE_NAME||' SET CURR_REC_INDCTR =''D'' , REC_START_DATE = current_timestamp where ' ||pky_COLUMN1|| ' in (Select '||pky_COLUMN1||' from (Select * from '||VIEW_NAME|| ' minus Select * from '||TABLE_NAME_STG|| ' ) DUMMY ) ' ;
CALL DBC.SYSEXECSQL(v_DELETE_sql);
SET v_UPDATE_sql = 'UPDATE '|| TABLE_NAME || ' SET CURR_REC_INDCTR = ''V'', REC_START_DATE = current_timestamp where ' ||pky_COLUMN1|| ' in (Select '||pky_COLUMN1||' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY ) ' ;
CALL DBC.SYSEXECSQL(v_UPDATE_sql);
SET v_INSERT2_sql= 'INSERT INTO ' || TABLE_NAME || ' Select current_timestamp, ' ||v_sql|| ' current_timestamp,current_timestamp,2,''U'' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY where '||pky_COLUMN1||' IN ( Select distinct '||pky_COLUMN1||' from '||VIEW_NAME||' )' ;
SET v_INSERT1_sql= 'INSERT INTO ' || TABLE_NAME || ' Select current_timestamp, ' ||v_sql|| ' current_timestamp,current_timestamp,1,''I'' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY where '||pky_COLUMN1||' NOT IN ( Select distinct '||pky_COLUMN1||' from '||VIEW_NAME||' ) ';
CALL DBC.SYSEXECSQL(v_INSERT2_sql);
CALL DBC.SYSEXECSQL(v_INSERT1_sql);
INSERT into TBL_LOG_DATA
VALUES (current_timestamp,:v_INSERT1_sql);
WHEN 2 THEN
SET v_DELETE_sql ='UPDATE '|| TABLE_NAME || ' SET CURR_REC_INDCTR = ''D'' , REC_START_DATE = current_timestamp where (' ||pky_COLUMN1|| ', '||pky_COLUMN2||') in (Select ' ||pky_COLUMN1|| ','||pky_COLUMN2||' from (Select * from '||VIEW_NAME|| ' minus Select * from '||TABLE_NAME_STG|| ' ) DUMMY ) ' ;
CALL DBC.SYSEXECSQL(v_DELETE_sql);
SET v_UPDATE_sql = 'UPDATE '|| TABLE_NAME || ' SET CURR_REC_INDCTR = ''V'' , REC_START_DATE = current_timestamp where (' ||pky_COLUMN1|| ', '||pky_COLUMN2||') in (Select ' ||pky_COLUMN1|| ','||pky_COLUMN2||' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY ) ' ;
CALL DBC.SYSEXECSQL(v_UPDATE_sql);
SET v_INSERT2_sql= 'INSERT INTO ' || TABLE_NAME || ' Select current_timestamp, ' ||v_sql|| ' current_timestamp,current_timestamp,2,''U'' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY where (' ||pky_COLUMN1|| ','||pky_COLUMN2||') IN ( Select distinct ' ||pky_COLUMN1|| ','||pky_COLUMN2||' from '||VIEW_NAME||' )' ;
SET v_INSERT1_sql= 'INSERT INTO ' || TABLE_NAME || ' Select current_timestamp, ' ||v_sql|| ' current_timestamp,current_timestamp,1,''I'' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY where (' ||pky_COLUMN1|| ','||pky_COLUMN2||') NOT IN ( Select distinct ' ||pky_COLUMN1|| ','||pky_COLUMN2||' from '||VIEW_NAME||' )' ;
CALL DBC.SYSEXECSQL(v_INSERT2_sql);
CALL DBC.SYSEXECSQL(v_INSERT1_sql);
WHEN 3 THEN
SET v_DELETE_sql ='UPDATE '|| TABLE_NAME || ' SET CURR_REC_INDCTR = ''D'' , REC_START_DATE = current_timestamp where (' ||pky_COLUMN1|| ', ' ||pky_COLUMN2|| ','||pky_COLUMN3||') in (Select '||pky_COLUMN1|| ', ' ||pky_COLUMN2|| ','||pky_COLUMN3||' (Select * from '||VIEW_NAME|| ' minus Select * from '||TABLE_NAME_STG|| ' ) DUMMY ) ' ;
CALL DBC.SYSEXECSQL(v_DELETE_sql);
SET v_UPDATE_sql = 'UPDATE '|| TABLE_NAME || ' SET CURR_REC_INDCTR = ''D'' , REC_START_DATE = current_timestamp where (' ||pky_COLUMN1|| ', ' ||pky_COLUMN2|| ','||pky_COLUMN3||') in (Select '||pky_COLUMN1|| ', ' ||pky_COLUMN2|| ','||pky_COLUMN3||' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY ) ' ;
CALL DBC.SYSEXECSQL(v_UPDATE_sql);
SET v_INSERT2_sql= 'INSERT INTO ' || TABLE_NAME || ' Select current_timestamp, ' ||v_sql|| ' current_timestamp,current_timestamp,2,''U'' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY where (' ||pky_COLUMN1|| ', ' ||pky_COLUMN2|| ','||pky_COLUMN3||') IN ( Select distinct ' ||pky_COLUMN1|| ', ' ||pky_COLUMN2|| ','||pky_COLUMN3|| ' from '||VIEW_NAME||' )' ;
SET v_INSERT1_sql= 'INSERT INTO ' || TABLE_NAME || ' Select current_timestamp, ' ||v_sql|| ' current_timestamp,current_timestamp,1,''I'' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY where (' ||pky_COLUMN1|| ', ' ||pky_COLUMN2|| ','||pky_COLUMN3||') NOT IN ( Select distinct ' ||pky_COLUMN1|| ', ' ||pky_COLUMN2|| ','||pky_COLUMN3||' from '||VIEW_NAME||' )' ;
CALL DBC.SYSEXECSQL(v_INSERT2_sql);
CALL DBC.SYSEXECSQL(v_INSERT1_sql);
WHEN 4 THEN
SET v_DELETE_sql = 'UPDATE '|| TABLE_NAME || ' SET CURR_REC_INDCTR = ''D'' , REC_START_DATE = current_timestamp where (' ||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||') in (Select ' ||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||' from (Select * from '||VIEW_NAME|| ' minus Select * from '||TABLE_NAME_STG|| ' ) DUMMY ) ' ;
CALL DBC.SYSEXECSQL(v_DELETE_sql);
SET v_UPDATE_sql = 'UPDATE '|| TABLE_NAME || ' SET CURR_REC_INDCTR = ''D'' , REC_START_DATE = current_timestamp where (' ||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||') in (Select ' ||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY ) ' ;
CALL DBC.SYSEXECSQL(v_UPDATE_sql);
SET v_INSERT2_sql= 'INSERT INTO ' || TABLE_NAME || ' Select current_timestamp, ' ||v_sql|| ' current_timestamp,current_timestamp,2,''U'' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY where (' ||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||') IN ( Select distinct ' ||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||' from '||VIEW_NAME||' )' ;
SET v_INSERT1_sql= 'INSERT INTO ' || TABLE_NAME || ' Select current_timestamp, ' ||v_sql|| ' current_timestamp,current_timestamp,1,''I'' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY where (' ||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||') NOT IN ( Select distinct '||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||' from '||VIEW_NAME||' )' ;
CALL DBC.SYSEXECSQL(v_INSERT2_sql);
CALL DBC.SYSEXECSQL(v_INSERT1_sql);
WHEN 5 THEN
SET v_DELETE_sql ='UPDATE '|| TABLE_NAME || ' SET CURR_REC_INDCTR = ''D'' , REC_START_DATE = current_timestamp where (' ||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||',' ||pky_COLUMN5|| ') in (Select '||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||',' ||pky_COLUMN5|| ' from (Select * from '||VIEW_NAME|| ' minus Select * from '||TABLE_NAME_STG|| ' ) DUMMY ) ' ;
CALL DBC.SYSEXECSQL(v_DELETE_sql);
SET v_UPDATE_sql = 'UPDATE '|| TABLE_NAME || ' SET CURR_REC_INDCTR = ''D'' , REC_START_DATE = current_timestamp where (' ||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||',' ||pky_COLUMN5|| ') in (Select '||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||',' ||pky_COLUMN5|| ' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY ) ' ;
CALL DBC.SYSEXECSQL(v_UPDATE_sql);
SET v_INSERT2_sql= 'INSERT INTO ' || TABLE_NAME || ' Select current_timestamp, ' ||v_sql|| ' current_timestamp,current_timestamp,2,''Y'' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY where (' ||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||',' ||pky_COLUMN5|| ') IN ( Select distinct ' ||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||',' ||pky_COLUMN5|| ' from '||VIEW_NAME||' )' ;
SET v_INSERT1_sql= 'INSERT INTO ' || TABLE_NAME || ' Select current_timestamp, ' ||v_sql|| ' current_timestamp,current_timestamp,1,''I'' from (Select * from '||TABLE_NAME_STG|| ' minus Select * from '||VIEW_NAME|| ' ) DUMMY where (' ||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||',' ||pky_COLUMN5|| ') NOT IN ( Select distinct ' ||pky_COLUMN1|| ',' ||pky_COLUMN2|| ','||pky_COLUMN3||','||pky_COLUMN4||',' ||pky_COLUMN5|| ' from '||VIEW_NAME||' )' ;
CALL DBC.SYSEXECSQL(v_INSERT2_sql);
CALL DBC.SYSEXECSQL(v_INSERT1_sql);
ELSE
INSERT into TBL_LOG_DATA
VALUES (current_timestamp,'Too Many Keys.. Time to add another case block');
END CASE ;
ELSEIF v_tab_count=0
or v_col_count=0 THEN
INSERT into TBL_LOG_DATA
VALUES (current_timestamp,'Problem with the load');
END IF ;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Audit
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- The below query returns Audit records, only when anything changes.
/*SET v_log_count_sql = 'INSERT INTO TBL_PSA_LOAD_STATISTICS( REC_LOAD_DATE,PSA_TABLE_NAME,LOAD_TYPE,REC_COUNT) Select current_timestamp,'''||TABLE_NAME||''' , CURR_REC_INDCTR, count(*) from '||TABLE_NAME||' where CURR_REC_INDCTR in (''I'',''D'',''U'')
GROUP by current_timestamp,'''||TABLE_NAME||''' , CURR_REC_INDCTR';
*/
-- The below query returns a zero record if there are no changes.
SET v_log_count_sql = 'INSERT INTO TBL_PSA_LOAD_STATISTICS( REC_LOAD_DATE,PSA_TABLE_NAME,LOAD_TYPE,REC_COUNT) Select current_timestamp,'''||TABLE_NAME||''' ,
CURR_REC_INDCTR, SUM(REC_COUNT) from (Select CURR_REC_INDCTR , count(*) REC_COUNT from '||TABLE_NAME||' where CURR_REC_INDCTR in (''I'',''D'',''U'')
GROUP by CURR_REC_INDCTR union
Select ''I'' CURR_REC_INDCTR, 0 REC_COUNT from sys_calendar.caldates where cdate =date
union
Select ''D'' CURR_REC_INDCTR, 0 REC_COUNT from sys_calendar.caldates where cdate =date
union
Select ''U'' CURR_REC_INDCTR, 0 REC_COUNT from sys_calendar.caldates where cdate =date) AUDIT
GROUP BY CURR_REC_INDCTR';
CALL DBC.SYSEXECSQL(v_log_count_sql);
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Set the indicators to 'Y' and 'N'
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SET v_FLAG_Y_sql = 'UPDATE ' ||TABLE_NAME||' SET CURR_REC_INDCTR = ''Y'' where CURR_REC_INDCTR in (''I'',''U'')';
CALL DBC.SYSEXECSQL(v_FLAG_Y_sql );
SET v_FLAG_N_sql = 'UPDATE ' ||TABLE_NAME||' SET CURR_REC_INDCTR =''N'' where CURR_REC_INDCTR in (''V'',''D'')';
CALL DBC.SYSEXECSQL(v_FLAG_N_sql );
END ;
