STEP 28 AIRCRAFT-ID

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

/*

Here we determine if any of these reports pertain to Aircraft that have been previously registered in CANADA by a model/serial number lookup against the CAWIS y04_aircraft_configuration table.

Note : SDR aircraft serial numbers are stored compressed, but 5008 file (Y04 table) aircraft serial number are stored in whatever format given to CAWIS by CCARCS.

*/

SELECT

'UPDATE M70_SDR_TRANSIT SET AIRCRAFT_ID = ' || Y04.AIRCRAFT_ID || ' WHERE SDR_NUMBER_NUM = ''' ||

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

FROM   M70_SDR_TRANSIT M70,

       Y04_AIRCRAFT_CONFIGURATION Y04

WHERE  Y04.PRODUCT_TYPE_CD = 'A'

AND    M70.AIRCRAFT_MODEL =  Y04.MODEL_ID

AND    M70.AIRCRAFT_SERIAL_NUMBER = DATA_COMPRESSION(Y04.SERIAL_ID)

AND    M70.AIRCRAFT_ID IS NULL

UNION

• ^2nd look for Bombardier data

SELECT

'UPDATE M70_SDR_TRANSIT SET AIRCRAFT_ID = ' || Y04.AIRCRAFT_ID || ' WHERE SDR_NUMBER_NUM = ''' ||

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

FROM   M70_SDR_TRANSIT M70,

       Y04_AIRCRAFT_CONFIGURATION Y04

WHERE  Y04.PRODUCT_TYPE_CD = 'A'

AND    M70.AIRCRAFT_SERIAL_NUMBER = DATA_COMPRESSION(Y04.SERIAL_ID)

AND    M70.AIRCRAFT_MODEL LIKE 'CL600%'

AND    Y04.MODEL_ID LIKE 'CL600%'

AND    M70.AIRCRAFT_ID IS NULL  ;

Sample data : Aircraft we were able to identify within the CAWIS registry data (y04) as having previously been registered in Canada before, however Aircraft_id is NOT mandatory in SDR data

Open