STEP 14 AIRCRAFT MANUFACTURER/MODEL

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

simply copy and place into your preferred SQL tool

OPTIONAL - if we do NOT have an AIRCRAFT MANUFACTURER/MODEL, but we DO have a tail mark, go to the FAA registry website and acquire the airframe info.

This query normally returns only 1 or 2 rows.

If the aircraft is amateur built, ignore.

If its a Canadian Type Certficiate Holder (TCH) (aka Canadian built), acquiring the airframe info is more important.

Alternately, if the registry page shows an aircraft that left the US before the SDR occurrence date, its a good idea to null out the tail mark

*/

SELECT SDR_NUMBER_NUM,

AIRCRAFT_MARK_ID AS XXX,

AIRCRAFT_MANUFACTURER,

AIRCRAFT_MODEL,

CHR(13) || 'UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MANUFACTURER_ID = ?, ' ||

'AIRCRAFT_MODEL = ''XXXX'',AIRCRAFT_SERIAL_NUMBER = ''XXXX'' WHERE SDR_NUMBER_NUM = ''' ||

SDR_NUMBER_NUM || ''';' UPD1, --- update with aircraft info

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

SDR_NUMBER_NUM || ''';' UPD2 --- or . . null out aircraft tail mark

FROM M70_SDR_TRANSIT

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

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