STEP 24 NATURE CODES

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

FAA DATA IMPORT PROCEDURE.TXT

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

/*