# Jacob and Daniel - MAT2

## CODE

### Ass2_DW_Creation_Script.SQL

DROP TABLE A2ERROREVENT CASCADE CONSTRAINTS;
DROP TABLE GENDERSPELLING CASCADE CONSTRAINTS;
DROP TABLE DWPROD CASCADE CONSTRAINTS;
DROP TABLE DWCUST CASCADE CONSTRAINTS;
DROP TABLE DWSALE CASCADE CONSTRAINTS;
DROP SEQUENCE ERROREVENTSEQ;
DROP SEQUENCE DWPRODIDSEQ;
DROP SEQUENCE DWCUSTIDSEQ;
DROP SEQUENCE DWSALEIDSEQ;

CREATE SEQUENCE DWSALEIDSEQ;
CREATE SEQUENCE DWPRODIDSEQ;
CREATE SEQUENCE DWCUSTIDSEQ;
CREATE SEQUENCE ERROREVENTSEQ;
--create the error event table
CREATE TABLE A2ERROREVENT (
	ERRORID INTEGER,
	SOURCE_ROWID ROWID,
	SOURCE_TABLE VARCHAR2(30),
	ERRORCODE INTEGER,
	FILTERID INTEGER,
	DATETIME DATE,
	ACTION VARCHAR2(6),
	CONSTRAINT ERROREVENTACTION
		CHECK (ACTION IN ('SKIP','MODIFY'))
);
/
CREATE TABLE DWPROD (
	DWPRODID NUMBER,
	DWSOURCETABLE VARCHAR2(128 BYTE),
	DWSOURCEID NUMBER,
	PRODNAME VARCHAR2(100 BYTE),
	PRODCATNAME VARCHAR2(30 BYTE),
	PRODMANUNAME VARCHAR2(30 BYTE),
	PRODSHIPNAME VARCHAR2(30 BYTE)
);
/
CREATE TABLE DWCUST (
	DWCUSTID NUMBER,
	DWSOURCEIDBRIS NUMBER,
	DWSOURCEIDMELB NUMBER,
	FIRSTNAME VARCHAR2(30 BYTE),
	SURNAME VARCHAR2(30 BYTE),
	GENDER VARCHAR2(10 BYTE),
	PHONE VARCHAR2(20 BYTE),
	POSTCODE NUMBER(4, 0),
	CITY VARCHAR2(50 BYTE),
	STATE VARCHAR2(10 BYTE),
	CUSTCATNAME VARCHAR2(30 BYTE)
);
/
CREATE TABLE DWSALE (
	DWSALEID NUMBER,
	DWCUSTID NUMBER,
	DWPRODID NUMBER,
	DWSOURCEIDBRIS NUMBER,
	DWSOURCEIDMELB NUMBER,
	QTY NUMBER,
	SALE_DWDATEID NUMBER,
	SALEPRICE NUMBER(7, 2)
);
/
--creates the gender spell checking table
CREATE TABLE GENDERSPELLING (
    BAD_STATUS VARCHAR2(20),
    GOOD_STATUS VARCHAR2(20)
);
/
INSERT INTO GENDERSPELLING VALUES ('MAIL','M');
INSERT INTO GENDERSPELLING VALUES ('WOMAN','F');
INSERT INTO GENDERSPELLING VALUES ('FEM','F');
INSERT INTO GENDERSPELLING VALUES ('FEMALE','F');
INSERT INTO GENDERSPELLING VALUES ('MALE','M');
INSERT INTO GENDERSPELLING VALUES ('GENTLEMAN','M');
INSERT INTO GENDERSPELLING VALUES ('MM','M');
INSERT INTO GENDERSPELLING VALUES ('FF','F');
INSERT INTO GENDERSPELLING VALUES ('FEMAIL','F');
/
-- Task 2.1 - Filter #1
INSERT INTO A2ERROREVENT (ERRORID, SOURCE_ROWID, SOURCE_TABLE, ERRORCODE, FILTERID, DATETIME, ACTION)
SELECT ERROREVENTSEQ.NEXTVAL, ROWID, 'A2PRODUCT', 116, 1, SYSDATE, 'SKIP' FROM A2PRODUCT WHERE PRODNAME IS NULL;
/
-- Task 2.2 - Filter #2
INSERT INTO A2ERROREVENT (ERRORID, SOURCE_ROWID, SOURCE_TABLE, ERRORCODE, FILTERID, DATETIME, ACTION)
SELECT ERROREVENTSEQ.NEXTVAL, ROWID, 'A2PRODUCT', 129, 2, SYSDATE, 'MODIFY'
FROM A2PRODUCT WHERE MANUFACTURERCODE IS NULL;
/
-- Task 2.3 - Filter #3
INSERT INTO A2ERROREVENT(ERRORID, SOURCE_ROWID, SOURCE_TABLE, ERRORCODE, FILTERID, DATETIME, ACTION)
SELECT ERROREVENTSEQ.NEXTVAL, ROWID, 'A2PRODUCT', 141, 3, SYSDATE, 'MODIFY'
FROM A2PRODUCT WHERE (PRODCATEGORY NOT IN
(SELECT PRODUCTCATEGORY FROM A2PRODCATEGORY)) OR (PRODCATEGORY IS NULL);
/
-- Task 2.4.1
-- SELECT * FROM A2PRODUCT WHERE ROWID NOT IN (SELECT SOURCE_ROWID FROM A2ERROREVENT);
-- Task 2.4.2
-- SELECT PRODID, PRODNAME, MAXQTY, PRODCATEGORY, A2PRODCATEGORY.CATEGORYNAME, SHIPPINGCODE, A2SHIPPING.DESCRIPTION, MANUFACTURERCODE, A2MANUFACTURER.MANUNAME, SELLPRICE FROM A2PRODUCT INNER JOIN A2PRODCATEGORY ON A2PRODUCT.PRODCATEGORY = A2PRODCATEGORY.PRODUCTCATEGORY INNER JOIN A2MANUFACTURER ON A2PRODUCT.MANUFACTURERCODE = A2MANUFACTURER.MANUCODE NATURAL JOIN A2SHIPPING WHERE A2PRODUCT.ROWID NOT IN (SELECT SOURCE_ROWID FROM A2ERROREVENT);

-- Task 2.4.3
INSERT INTO DWPROD (DWPRODID, DWSOURCETABLE, DWSOURCEID, PRODNAME, PRODCATNAME, PRODMANUNAME, PRODSHIPNAME)
SELECT DWPRODIDSEQ.NEXTVAL, 'A2PRODUCT', PRODID, PRODNAME, A2PRODCATEGORY.CATEGORYNAME, A2MANUFACTURER.MANUNAME, A2SHIPPING.DESCRIPTION
FROM A2PRODUCT
INNER JOIN A2PRODCATEGORY ON A2PRODUCT.PRODCATEGORY = A2PRODCATEGORY.PRODUCTCATEGORY
INNER JOIN A2MANUFACTURER ON A2PRODUCT.MANUFACTURERCODE = A2MANUFACTURER.MANUCODE
INNER JOIN A2SHIPPING ON A2PRODUCT.SHIPPINGCODE = A2SHIPPING.SHIPPINGCODE
WHERE A2PRODUCT.ROWID NOT IN (SELECT SOURCE_ROWID FROM A2ERROREVENT);
/
-- Task 2.4.4
INSERT INTO DWPROD (DWPRODID, DWSOURCETABLE, DWSOURCEID, PRODNAME, PRODCATNAME, PRODMANUNAME, PRODSHIPNAME)
SELECT DWPRODIDSEQ.NEXTVAL, 'A2PRODUCT', PRODID, PRODNAME, A2PRODCATEGORY.CATEGORYNAME, 'UNKNOWN', A2SHIPPING.DESCRIPTION FROM A2PRODUCT
INNER JOIN A2ERROREVENT ON A2PRODUCT.ROWID = A2ERROREVENT.SOURCE_ROWID
INNER JOIN A2PRODCATEGORY ON A2PRODUCT.PRODCATEGORY = A2PRODCATEGORY.PRODUCTCATEGORY
INNER JOIN A2SHIPPING ON A2PRODUCT.SHIPPINGCODE = A2SHIPPING.SHIPPINGCODE
WHERE A2ERROREVENT.FILTERID = 2;
/
-- Task 2.4.5
INSERT INTO DWPROD (DWPRODID, DWSOURCETABLE, DWSOURCEID, PRODNAME, PRODCATNAME, PRODMANUNAME, PRODSHIPNAME)
SELECT DWPRODIDSEQ.NEXTVAL, 'A2PRODUCT', PRODID, PRODNAME, 'UNKNOWN', A2MANUFACTURER.MANUNAME, A2SHIPPING.DESCRIPTION FROM A2PRODUCT
INNER JOIN A2ERROREVENT ON A2PRODUCT.ROWID = A2ERROREVENT.SOURCE_ROWID
INNER JOIN A2MANUFACTURER ON A2PRODUCT.MANUFACTURERCODE = A2MANUFACTURER.MANUCODE
INNER JOIN A2SHIPPING ON A2PRODUCT.SHIPPINGCODE = A2SHIPPING.SHIPPINGCODE
WHERE A2ERROREVENT.FILTERID = 3;
/
-- Task 3.1 - Filter #4
INSERT INTO A2ERROREVENT (ERRORID, SOURCE_ROWID, SOURCE_TABLE, ERRORCODE, FILTERID, DATETIME, ACTION)
SELECT ERROREVENTSEQ.NEXTVAL, ROWID, 'A2CUSTBRIS', 168, 4, SYSDATE, 'MODIFY' FROM A2CUSTBRIS
WHERE (CUSTCATCODE NOT IN (SELECT CUSTCATCODE FROM A2CUSTCATEGORY)) OR CUSTCATCODE IS NULL;
/
-- TASK 3.2 -FILTER #5
INSERT INTO A2ERROREVENT(ERRORID, SOURCE_ROWID, SOURCE_TABLE, ERRORCODE, FILTERID, DATETIME, ACTION)
SELECT ERROREVENTSEQ.NEXTVAL, ROWID, 'A2CUSTBRIS', 196, 5, SYSDATE, 'MODIFY' FROM A2CUSTBRIS 
WHERE PHONE LIKE '%-%' OR PHONE LIKE '% %';
/
-- TAKS 3.3 -FILTER #6
INSERT INTO A2ERROREVENT(ERRORID, SOURCE_ROWID, SOURCE_TABLE, ERRORCODE, FILTERID, DATETIME, ACTION)
SELECT ERROREVENTSEQ.NEXTVAL, ROWID, 'A2CUSTBRIS', 204, 6, SYSDATE, 'SKIP' FROM A2CUSTBRIS 
WHERE LENGTH(PHONE) <> 10 AND PHONE NOT LIKE '% %' AND PHONE NOT LIKE '%-%';
/
-- TAKS 3.4 -FILTER #7
INSERT INTO A2ERROREVENT(ERRORID, SOURCE_ROWID, SOURCE_TABLE, ERRORCODE, FILTERID, DATETIME, ACTION)
SELECT ERROREVENTSEQ.NEXTVAL, ROWID, 'A2CUSTBRIS', 231, 7, SYSDATE, 'MODIFY' FROM A2CUSTBRIS 
WHERE UPPER(GENDER) NOT IN ('M', 'F') or GENDER is NULL;
/
-- TASK 3.5.1
INSERT INTO DWCUST (DWCUSTID, DWSOURCEIDBRIS, DWSOURCEIDMELB, FIRSTNAME, SURNAME, GENDER, PHONE, POSTCODE, CITY, STATE, CUSTCATNAME)
SELECT DWCUSTIDSEQ.NEXTVAL, CUSTID, NULL, FNAME, SNAME, GENDER, PHONE, POSTCODE, CITY, STATE, A2CUSTCATEGORY.CUSTCATNAME FROM A2CUSTBRIS
INNER JOIN A2CUSTCATEGORY ON A2CUSTCATEGORY.CUSTCATCODE = A2CUSTBRIS.CUSTCATCODE
WHERE A2CUSTBRIS.ROWID NOT IN (SELECT SOURCE_ROWID FROM A2ERROREVENT);
/
-- TASK 3.5.2
-- INSERT INTO DWCUST (DWCUSTID, DWSOURCEIDBRIS, DWSOURCEIDMELB, FIRSTNAME, SURNAME, GENDER, PHONE, POSTCODE,CITY, STATE, CUSTCATNAME) SELECT DWCUSTIDSEQ.NEXTVAL, CUSTID, NULL, FNAME, SNAME, GENDER, PHONE, POSTCODE, CITY, STATE, 'UNKNOWN' FROM A2CUSTBRIS WHERE A2CUSTBRIS.ROWID IN (SELECT SOURCE_ROWID FROM A2ERROREVENT WHERE FILTERID = 4);
INSERT INTO DWCUST (DWCUSTID, DWSOURCEIDBRIS, FIRSTNAME, SURNAME, GENDER, PHONE, POSTCODE, CITY, STATE, CUSTCATNAME)
SELECT DWCUSTIDSEQ.NEXTVAL, CUSTID, FNAME, SNAME, GENDER, PHONE, POSTCODE, CITY, STATE, 'UNKNOWN' FROM A2CUSTBRIS
WHERE ROWID IN (SELECT SOURCE_ROWID FROM A2ERROREVENT WHERE FILTERID = 4);
/
-- TASK 3.5.3
INSERT INTO DWCUST (DWCUSTID, DWSOURCEIDBRIS, DWSOURCEIDMELB, FIRSTNAME, SURNAME, GENDER, PHONE, POSTCODE,
CITY, STATE, CUSTCATNAME)
SELECT DWCUSTIDSEQ.NEXTVAL, CUSTID, NULL, FNAME, SNAME, GENDER, REPLACE(REPLACE(PHONE, '-', ''), ' ', '') AS PHONE, POSTCODE, CITY, STATE, A2CUSTCATEGORY.CUSTCATNAME FROM A2CUSTBRIS
INNER JOIN A2CUSTCATEGORY ON A2CUSTCATEGORY.CUSTCATCODE = A2CUSTBRIS.CUSTCATCODE
WHERE A2CUSTBRIS.ROWID IN (SELECT SOURCE_ROWID FROM A2ERROREVENT WHERE FILTERID = 5);
/
--TASK 3.5.4
INSERT INTO DWCUST (DWCUSTID, DWSOURCEIDBRIS, FIRSTNAME, SURNAME, GENDER, PHONE, POSTCODE, CITY, STATE, CUSTCATNAME)
SELECT DWCUSTIDSEQ.NEXTVAL, CUSTID, FNAME, SNAME, NVL(GENDERSPELLING.GOOD_STATUS, 'U'), PHONE, POSTCODE, CITY, STATE, A2CUSTCATEGORY.CUSTCATCODE FROM A2CUSTBRIS
INNER JOIN A2CUSTCATEGORY ON A2CUSTCATEGORY.CUSTCATCODE = A2CUSTBRIS.CUSTCATCODE
LEFT OUTER JOIN GENDERSPELLING ON GENDERSPELLING.BAD_STATUS = A2CUSTBRIS.GENDER
WHERE A2CUSTBRIS.ROWID IN (SELECT SOURCE_ROWID FROM A2ERROREVENT WHERE FILTERID = 7);
/
--Task 4.1
--MERGE INTO DWCUST DW
--USING (SELECT CUSTID, FNAME, SNAME, GENDER, PHONE, POSTCODE, CITY, STATE, A2CUSTCATEGORY.CUSTCATNAME AS CUSTCATNAME FROM A2CUSTMELB INNER JOIN A2CUSTCATEGORY ON A2CUSTCATEGORY.CUSTCATCODE = A2CUSTMELB.CUSTCATCODE) MELB
--ON (DW.FIRSTNAME = MELB.FNAME AND DW.SURNAME = MELB.SNAME AND DW.POSTCODE = MELB.POSTCODE)
--WHEN MATCHED THEN
  --  UPDATE SET DW.DWSOURCEIDMELB = MELB.CUSTID
--WHEN NOT MATCHED THEN
   -- INSERT (DW.DWCUSTID, DW.DWSOURCEIDMELB, DW.FIRSTNAME, DW.SURNAME, DW.GENDER, DW.PHONE, DW.POSTCODE, DW.CITY, DW.STATE, DW.CUSTCATNAME)
    --VALUES (DWCUSTIDSEQ.NEXTVAL, MELB.CUSTID, MELB.FNAME, MELB.SNAME, UPPER(MELB.GENDER), MELB.PHONE, MELB.POSTCODE, MELB.CITY, MELB.STATE, MELB.CUSTCATNAME);
/
-- Task 5.1 - Filter #8
INSERT INTO A2ERROREVENT (ERRORID, SOURCE_ROWID, SOURCE_TABLE, ERRORCODE, FILTERID, DATETIME, ACTION)
SELECT ERROREVENTSEQ.NEXTVAL, ROWID, 'A2SALEBRIS', 247, 8, SYSDATE, 'SKIP' FROM A2SALEBRIS WHERE PRODID NOT IN (SELECT DWSOURCEID FROM DWPROD) OR PRODID IS NULL;
/
-- Task 5.2 - Filter #9
INSERT INTO A2ERROREVENT (ERRORID, SOURCE_ROWID, SOURCE_TABLE, ERRORCODE, FILTERID, DATETIME, ACTION)
SELECT ERROREVENTSEQ.NEXTVAL, ROWID, 'A2SALEBRIS', 272, 9, SYSDATE, 'SKIP' FROM A2SALEBRIS WHERE CUSTID NOT IN (SELECT DWSOURCEIDBRIS FROM DWCUST) OR CUSTID IS NULL;
/
-- Task 5.3 - Filter #10
INSERT INTO A2ERROREVENT (ERRORID, SOURCE_ROWID, SOURCE_TABLE, ERRORCODE, FILTERID, DATETIME, ACTION)
SELECT ERROREVENTSEQ.NEXTVAL, ROWID, 'A2SALEBRIS', 294, 10, SYSDATE, 'MODIFY' FROM A2SALEBRIS WHERE SHIPDATE < SALEDATE; -- Assuming date format is used in rows, may need to convert
/
-- Task 5.4 - Filter #11
INSERT INTO A2ERROREVENT (ERRORID, SOURCE_ROWID, SOURCE_TABLE, ERRORCODE, FILTERID, DATETIME, ACTION)
SELECT ERROREVENTSEQ.NEXTVAL, ROWID, 'A2SALEBRIS', 317, 11, SYSDATE, 'MODIFY' FROM A2SALEBRIS WHERE UNITPRICE IS NULL;
/
--TASK 5.5
INSERT INTO DWSALE (DWSALEID, DWCUSTID, DWPRODID, DWSOURCEIDBRIS, DWSOURCEIDMELB, QTY, SALE_DWDATEID, SALEPRICE)
SELECT DWSALEIDSEQ.NEXTVAL, DWCUST.DWCUSTID, DWPROD.DWPRODID, SALEID, NULL, QTY, TO_NUMBER(TO_CHAR(SALEDATE, 'yyyymmddhh24miss')), UNITPRICE
FROM A2SALEBRIS
INNER JOIN DWCUST ON DWCUST.DWSOURCEIDBRIS = A2SALEBRIS.CUSTID
INNER JOIN DWPROD ON DWPROD.DWSOURCEID = A2SALEBRIS.PRODID
WHERE A2SALEBRIS.ROWID NOT IN (SELECT SOURCE_ROWID FROM A2ERROREVENT);
/
--task 6.1 Filter #12
INSERT INTO A2ERROREVENT (ERRORID, SOURCE_ROWID, SOURCE_TABLE, ERRORCODE, FILTERID, DATETIME, ACTION)
SELECT ERROREVENTSEQ.NEXTVAL, ROWID, 'A2SALEMELB', 325, 12, SYSDATE, 'SKIP' FROM A2SALEMELB WHERE PRODID NOT IN (SELECT DWSOURCEID FROM DWPROD)
OR PRODID IS NULL;
/
--task 6.1 Filter #13
INSERT INTO A2ERROREVENT (ERRORID, SOURCE_ROWID, SOURCE_TABLE, ERRORCODE, FILTERID, DATETIME, ACTION)
SELECT ERROREVENTSEQ.NEXTVAL, ROWID, 'A2SALEMELB', 346, 13, SYSDATE, 'SKIP' FROM A2SALEMELB WHERE CUSTID NOT IN (SELECT DWSOURCEIDMELB FROM DWCUST) OR CUSTID IS NULL;
/
--task 6.1 Filter #14
INSERT INTO A2ERROREVENT (ERRORID, SOURCE_ROWID, SOURCE_TABLE, ERRORCODE, FILTERID, DATETIME, ACTION)
SELECT ERROREVENTSEQ.NEXTVAL, ROWID, 'A2SALEMELB', 379, 14, SYSDATE, 'MODIFY' FROM A2SALEMELB WHERE SHIPDATE < SALEDATE;
/
--task 6.1 Filter #15
INSERT INTO A2ERROREVENT (ERRORID, SOURCE_ROWID, SOURCE_TABLE, ERRORCODE, FILTERID, DATETIME, ACTION)
SELECT ERROREVENTSEQ.NEXTVAL, ROWID, 'A2SALEMELB', 391, 15, SYSDATE, 'MODIFY' FROM A2SALEMELB WHERE UNITPRICE IS NULL;
/

### Ass2_DW_Queries.SQL

## TODO
- Credit Tasks
- Task 5.5
- Task 5.6
- Task 5.7
- Task 6.2 (Filter #13)
- Task 6.5
- Task 6.6
- Task 6.7
- Part 8 Queries
- A
- B
- C
- D
- E
- F