Nature code #1 is mandatory and is converted thru the first series of statements below. "O" - other is the default if the code still comes up invalid or Null.
The documentation on the FAA website regarding codes has not been updated for a decade or so. FAA SDR management is aware of the issue.
http://av-info.faa.gov/data/SDRS/SDRCODES.DOC
So new/bad codes are simply altered to defaults like "other"
Nature codes #2 & #3 are optional
*/
--- NATURE CODE 1 -- MANDATORY
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_1 = 'B1' WHERE NATURE_CD_1 = 'B';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_1 = 'D1' WHERE NATURE_CD_1 = 'D';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_1 = 'E1' WHERE NATURE_CD_1 = 'E';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_1 = 'E2' WHERE NATURE_CD_1 = 'Q';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_1 = 'E3' WHERE NATURE_CD_1 = 'R';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_1 = 'E4' WHERE NATURE_CD_1 = 'T';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_1 = 'E5' WHERE NATURE_CD_1 = 'X';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_1 = 'E6' WHERE NATURE_CD_1 = 'Y';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_1 = 'F1' WHERE NATURE_CD_1 = 'F';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_1 = 'F2' WHERE NATURE_CD_1 = 'I';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_1 = 'H1' WHERE NATURE_CD_1 = 'H';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_1 = 'J1' WHERE NATURE_CD_1 = 'J';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_1 = 'J2' WHERE NATURE_CD_1 = 'N';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_1 = 'J3' WHERE NATURE_CD_1 = 'P';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_1 = 'K1' WHERE NATURE_CD_1 = 'K';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_1 = 'U2' WHERE NATURE_CD_1 = 'U';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_1 = 'U1' WHERE NATURE_CD_1 = '1';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_1 = 'Q' WHERE NATURE_CD_1 = 'W';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_1 = 'G' WHERE NATURE_CD_1 = 'Z';
COMMIT ;
---
UPDATE M70_SDR_TRANSIT M70
SET NATURE_CD_1 = 'O' -- other
WHERE NVL(NATURE_CD_1,'#')
NOT IN ( SELECT NATURE_CD FROM TR98_NATURE );
COMMIT;
--- NATURE CODE 2
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_2 = 'B1' WHERE NATURE_CD_2 = 'B';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_2 = 'D1' WHERE NATURE_CD_2 = 'D';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_2 = 'E1' WHERE NATURE_CD_2 = 'E';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_2 = 'E2' WHERE NATURE_CD_2 = 'Q';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_2 = 'E3' WHERE NATURE_CD_2 = 'R';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_2 = 'E4' WHERE NATURE_CD_2 = 'T';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_2 = 'E5' WHERE NATURE_CD_2 = 'X';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_2 = 'E6' WHERE NATURE_CD_2 = 'Y';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_2 = 'F1' WHERE NATURE_CD_2 = 'F';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_2 = 'F2' WHERE NATURE_CD_2 = 'I';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_2 = 'H1' WHERE NATURE_CD_2 = 'H';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_2 = 'J1' WHERE NATURE_CD_2 = 'J';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_2 = 'J2' WHERE NATURE_CD_2 = 'N';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_2 = 'J3' WHERE NATURE_CD_2 = 'P';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_2 = 'K1' WHERE NATURE_CD_2 = 'K';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_2 = 'U2' WHERE NATURE_CD_2 = 'U';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_2 = 'U1' WHERE NATURE_CD_2 = '1';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_2 = 'Q' WHERE NATURE_CD_2 = 'W';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_2 = 'G' WHERE NATURE_CD_2 = 'Z';
UPDATE M70_SDR_TRANSIT M70
SET NATURE_CD_2 = 'O'
WHERE TRIM(NATURE_CD_2)
NOT IN ( SELECT NATURE_CD FROM TR98_NATURE );
COMMIT;
--- NATURE CODE 3
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_3 = 'B1' WHERE NATURE_CD_3 = 'B';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_3 = 'D1' WHERE NATURE_CD_3 = 'D';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_3 = 'E1' WHERE NATURE_CD_3 = 'E';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_3 = 'E2' WHERE NATURE_CD_3 = 'Q';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_3 = 'E3' WHERE NATURE_CD_3 = 'R';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_3 = 'E4' WHERE NATURE_CD_3 = 'T';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_3 = 'E5' WHERE NATURE_CD_3 = 'X';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_3 = 'E6' WHERE NATURE_CD_3 = 'Y';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_3 = 'F1' WHERE NATURE_CD_3 = 'F';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_3 = 'F2' WHERE NATURE_CD_3 = 'I';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_3 = 'H1' WHERE NATURE_CD_3 = 'H';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_3 = 'J1' WHERE NATURE_CD_3 = 'J';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_3 = 'J2' WHERE NATURE_CD_3 = 'N';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_3 = 'J3' WHERE NATURE_CD_3 = 'P';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_3 = 'K1' WHERE NATURE_CD_3 = 'K';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_3 = 'U2' WHERE NATURE_CD_3 = 'U';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_3 = 'U1' WHERE NATURE_CD_3 = '1';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_3 = 'Q' WHERE NATURE_CD_3 = 'W';
UPDATE M70_SDR_TRANSIT M70 SET NATURE_CD_3 = 'G' WHERE NATURE_CD_3 = 'Z';
UPDATE M70_SDR_TRANSIT M70
SET NATURE_CD_3 = 'O'
WHERE TRIM(NATURE_CD_3)
NOT IN ( SELECT NATURE_CD FROM TR98_NATURE );
COMMIT;
UPDATE M70_SDR_TRANSIT SET NATURE_CD_2 = NULL WHERE SUBSTR(NATURE_CD_2,1,1) = ' ' ;
UPDATE M70_SDR_TRANSIT SET NATURE_CD_3 = NULL WHERE SUBSTR(NATURE_CD_3,1,1) = ' ' ;
commit;
--- DOUBLE CHECK FOR INVALID NATURE CODES
SELECT DISTINCT(M70.NATURE_CD_1) FROM M70_SDR_TRANSIT M70
WHERE NOT EXISTS
(SELECT TR98.NATURE_CD FROM TR98_NATURE TR98 WHERE TR98.NATURE_CD = M70.NATURE_CD_1)
UNION
SELECT DISTINCT(M70.NATURE_CD_2) FROM M70_SDR_TRANSIT M70
WHERE NOT EXISTS
(SELECT TR98.NATURE_CD FROM TR98_NATURE TR98 WHERE TR98.NATURE_CD = M70.NATURE_CD_2)
UNION
SELECT DISTINCT(M70.NATURE_CD_3) FROM M70_SDR_TRANSIT M70
WHERE NOT EXISTS
(SELECT TR98.NATURE_CD FROM TR98_NATURE TR98 WHERE TR98.NATURE_CD = M70.NATURE_CD_3) ;
/*
0 Comments