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 ),' ') = ' ' ;
Add Comment