220
edits
Changes
Jump to navigation
Jump to search
→Cleaning ipos table
GROUP BY issuer, issuedate, statecode;
--9470
Now use the ipoinclude table to create a ipocore table. Then check to see if this core table has unique keys so 1 key matches with 1 record. This is the defining characteristic of a core table.
DROP TABLE ipocore;
CREATE TABLE ipocore AS
SELECT ipos.issuer, ipos.issuedate, ipos.statecode
FROM ipos INNER JOIN ipoinclude ON ipos.issuer = ipoinclude.issuer AND ipos.issuedate = ipoinclude.issuedate AND
ipos.statecode = ipoinclude.statecode AND ipos.principalamt = ipoinclude.principalamt;
SELECT COUNT(*)
FROM (SELECT DISTINCT issuer, issuedate, statecode FROM ipocore)a;
You should notice that the ipocore table count does not match the count of DISTINCT keys. This means there are still some duplicates. So I created another duplicate table.
DROP TABLE ipoduplicates2;
CREATE TABLE ipoduplicates2 AS
SELECT *, COUNT(*)
FROM (SELECT issuer, issuedate, statecode FROM ipocore)a
GROUP BY issuer, issuedate, statecode
HAVING COUNT(*) > 1;
Then I created DELETE statements for all these entries. I deleted them from the ipoinclude table which will prevent these keys from appearing in the ipocore table when you JOIN the ipos with ipoinclude table.
--manually remove bad keys
DELETE FROM ipoinclude WHERE issuer = 'PacTel Corp' AND statecode = 'CA';
--1
DELETE FROM ipoinclude WHERE issuer = 'Templeton Dragon Fund Inc' AND statecode = 'FL';
--1
DELETE FROM ipoinclude WHERE issuer = 'Sterling Commerce' AND statecode = 'TX';
--1
DELETE FROM ipoinclude WHERE issuer = 'Sothebys Holdings Inc' AND statecode = 'NY';
--1
DELETE FROM ipoinclude WHERE issuer = 'TD Waterhouse Group Inc' AND statecode = 'NY';
--1
DELETE FROM ipoinclude WHERE issuer = 'Berlitz International Inc' AND statecode = 'NJ';
--1
DELETE FROM ipoinclude WHERE issuer = 'Spain Fund Inc' AND statecode = 'NY';
--1
DELETE FROM ipoinclude WHERE issuer = 'Ultramar Corp' AND statecode = 'CT';
--1
DELETE FROM ipoinclude WHERE issuer = 'Goldman Sachs Group Inc' AND statecode = 'NY';
--1
DELETE FROM ipoinclude WHERE issuer = 'Fidelity Advisor Korea Fund' AND statecode = 'MA';
--1
DELETE FROM ipoinclude WHERE issuer = 'Euronet Services Inc' AND statecode = 'KS';
--1
DELETE FROM ipoinclude WHERE issuer = 'Emerging Markets Tele Fund Inc' AND statecode = 'NY';
--1
DELETE FROM ipoinclude WHERE issuer = 'FirstMiss Gold Inc' AND statecode = 'NV';
--1
DELETE FROM ipoinclude WHERE issuer = 'Templeton Vietnam Opportunitie' AND statecode = 'FL';
--1
DELETE FROM ipoinclude WHERE issuer = 'Hybridon Inc' AND statecode = 'MA';
--1
DELETE FROM ipoinclude WHERE issuer = 'Indonesia Fund Inc' AND statecode = 'NY';
--1
DELETE FROM ipoinclude WHERE issuer = 'OpenTV Corp' AND statecode = 'CA';
--2
DELETE FROM ipoinclude WHERE issuer = 'Scudder New Europe Fund' AND statecode = 'NY';
--2
DELETE FROM ipoinclude WHERE issuer = 'Austria Fund Inc' AND statecode = 'NY';
--2
==Creating Stage Flags Table==
