I need your help in doing some pl/sql coding in comparing data between two tables. I was able to write some SQL but not familiar with writing pl/sql code to run as part of an ETL process. I'll need to compare the record_count in each table by replacing the 'tbl_' part in the target. Please let me know if you need any other information in order for you to help me.
DROP TABLE application_source PURGE;
CREATE TABLE application_source
(
created_dt DATE,
application_name VARCHAR2(30),
record_count NUMBER
)
PARTITION BY RANGE (created_dt)
(PARTITION P20170815 VALUES LESS THAN (TO_DATE('2017-08-16', 'YYYY-MM-DD'))
)
;
INSERT ALL
INTO application_source(created_dt, application_name, record_count) VALUES(TRUNC(SYSDATE)-1, 'employees', 20)
INTO application_source(created_dt, application_name, record_count) VALUES(TRUNC(SYSDATE)-1, 'vendors', 7)
INTO application_source(created_dt, application_name, record_count) VALUES(TRUNC(SYSDATE)-1, 'others', 3)
SELECT * FROM DUAL;
DROP TABLE application_target PURGE;
CREATE TABLE application_target
(
created_dt DATE,
application_name VARCHAR2(40),
record_count NUMBER
)
PARTITION BY RANGE (created_dt)
(PARTITION P20170815 VALUES LESS THAN (TO_DATE('2017-08-16', 'YYYY-MM-DD'))
)
;
INSERT ALL
INTO application_target(created_dt, application_name, record_count) VALUES(TRUNC(SYSDATE)-1, 'tbl_employees', 20)
INTO application_target(created_dt, application_name, record_count) VALUES(TRUNC(SYSDATE)-1, 'tbl_vendors', 7)
INTO application_target(created_dt, application_name, record_count) VALUES(TRUNC(SYSDATE)-1, 'tbl_others', 3)
SELECT * FROM DUAL;
DECLARE src_count application_source.record_count%type;
tgt_count application_target.record_count%type;
i_application_nm application_target.application_name%type;
BEGIN
SELECT record_count
INTO src_count
FROM applciation_source
WHERE created_dt = TRUNC(SYSDATE)-1
AND application_name = i_application_nm;
END;
BEGIN
SELECT record_count
INTO tgt_count
FROM applciation_target
WHERE created_dt = TRUNC(SYSDATE)-1
AND application_name = REPLACE(i_application_nm, 'tbl', '');
END;
CASE WHEN src_count = tgt_count
dbms_output.put_line('Count matches between employees and tbl_employees for this date. Continue to next table vendors');
/