1. Backup Polk data into temp tables.
CREATE TABLE DBO_POLK.POLK_MAPPED_FULL_BKUP
(VEHICLE_YEAR NUMBER(10
MAKE_ID NUMBER(10,0),
MODEL_ID NUMBER(10,0),
SUBMODEL_ID NUMBER(10,0),
BODYSTYLE_ID NUMBER(10,0),
DRIVETRAIN_ID NUMBER(10,0),
ENGINE_ID NUMBER(10,0),
ASPIRATION_ID NUMBER(10,0),
FUELTYPE_ID NUMBER(10,0),
FUELDELIVERY_ID NUMBER(10,0),
TEMP_KEY_ID NUMBER(10,0) )
TABLESPACE POLK;
INSERT INTO DBO_POLK.POLK_MAPPED_FULL_BKUP
SELECT * FROM DBO_POLK.POLK_MAPPED_FULL;
CREATE TABLE DBO_POLK.POLK_MAPPED_FULL_ZIP_BKUP
(TEMP_KEY_ID NUMBER,
ZIP NUMBER,
VEHICLE_COUNT NUMBER)
TABLESPACE POLK;
INSERT INTO DBO_POLK.POLK_MAPPED_FULL_ZIP_BKUP
SELECT * FROM DBO_POLK.POLK_MAPPED_FULL_ZIPS;
CREATE OR REPLACE VIEW DBO_POLK.MIO_NEW_V_COUNT AS
SELECT t.ZIP, SUM(t.VEHICLE_COUNT) "New_V_Count"
FROM DBO_POLK.POLK_MAPPED_FULL_ZIPS t
GROUP BY t.ZIP;
CREATE OR REPLACE VIEW DBO_POLK.MIO_Old_V_COUNT AS
SELECT t.ZIP, SUM(t.VEHICLE_COUNT) "Old_V_Count"
FROM DBO_POLK.POLK_MAPPED_FULL_ZIP_BKUP t
GROUP BY t.ZIP;
SELECT t.ZIP, t."New_V_Count", y."Old_V_Count",(t."New_V_Count" - y."Old_V_Count") "Diff"
FROM DBO_POLK.MIO_NEW_V_COUNT t, DBO_POLK.MIO_Old_V_COUNT y
WHERE y.ZIP = t.ZIP
AND t."New_V_Count" <> y."Old_V_Count"
ORDER BY (t."New_V_Count" - y."Old_V_Count") DESC;
CREATE OR REPLACE VIEW DBO_POLK.MIO_Old_MAPPED_FULL AS
SELECT VEHICLE_YEAR, count(*) "OldTotal_V_Year"
FROM DBO_POLK.POLK_MAPPED_FULL_BKUP t
group by VEHICLE_YEAR;
CREATE OR REPLACE VIEW DBO_POLK.MIO_New_MAPPED_FULL AS
SELECT VEHICLE_YEAR, count(*) "NewTotal_V_Year"
FROM DBO_POLK.POLK_MAPPED_FULL t
group by VEHICLE_YEAR;
select p.vehicle_year, t."OldTotal_V_Year", p."NewTotal_V_Year"
from DBO_POLK.MIO_New_MAPPED_FULL p, DBO_POLK.MIO_Old_MAPPED_FULL t
where p.vehicle_year = t.vehicle_year
2. Delete temp tables and view