STEP 15 AIRCRAFT SERIAL NUMBER

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

simply copy and place into your preferred SQL tool

OPTIONAL - if we have a tail mark, and airframe data, BUT no aircraft serial number retrieve it from the FAA register, or null out that tail mark. Serial numbers are important to help us associate the reports to previously Canadian-registered aircraft by establishing a possible Aircraft_id. (unique numeric identifier for a specific aircraft, provided by CCARCS)

*/

--- ACQUIRE MISSING AIRCRAFT SERIAL BY TAIL-MARK/MODEL MATCH

--- TO ANOTHER REPORT IN THIS BATCH ??

SELECT

'UPDATE M70_SDR_TRANSIT SET AIRCRAFT_SERIAL_NUMBER = ''' || M70B.AIRCRAFT_SERIAL_NUMBER ||

''' WHERE SDR_NUMBER_NUM = ''' || M70.SDR_NUMBER_NUM || ''' ; ' AS UPD1

FROM M70_SDR_TRANSIT M70,

M70_SDR_TRANSIT M70B

WHERE M70.AIRCRAFT_SERIAL_NUMBER IS NULL

AND M70.AIRCRAFT_MARK_ID IS NOT NULL

AND M70B.AIRCRAFT_MARK_ID = M70.AIRCRAFT_MARK_ID

AND M70B.AIRCRAFT_MODEL = M70.AIRCRAFT_MODEL

AND M70B.AIRCRAFT_SERIAL_NUMBER IS NOT NULL;

--- RETRIEVE AIRCRAFT-SERIAL FROM FAA WEB SITE ??

SELECT SDR_NUMBER_NUM,

AIRCRAFT_MARK_ID AS MRK,

AIRCRAFT_MANUFACTURER,

AIRCRAFT_MODEL,

CHR(13) ||

'UPDATE M70_SDR_TRANSIT SET AIRCRAFT_SERIAL_NUMBER = ''XXXX'' WHERE AIRCRAFT_MARK_ID = ''' ||

AIRCRAFT_MARK_ID || ''';' as UPD1 ,

'UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MARK_ID = NULL WHERE SDR_NUMBER_NUM = ''' ||

SDR_NUMBER_NUM || ''';' AS UPD2

FROM M70_SDR_TRANSIT

WHERE NVL(TRIM(AIRCRAFT_MARK_ID ),' ') <> ' '

AND NVL(TRIM(AIRCRAFT_MANUFACTURER ),' ') <> ' '

AND NVL(TRIM(AIRCRAFT_SERIAL_NUMBER),' ') = ' ' ;

/*