STEP 24 NATURE CODES

Sept 2021 : NOTE - all required code for this process is found at :

simply copy and place into your preferred SQL tool

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) ;

/*