STEP 12 Check for NON-American tail marks
- kurt.martin
Sept 2021 : NOTE - all required code for this process is found at :
simply copy and place into your preferred SQL tool
–
*/
SELECT DISTINCT(AIRCRAFT_MARK_ID) FROM M70_SDR_TRANSIT
WHERE NVL(AIRCRAFT_MARK_ID,'N') NOT LIKE 'N%';
/*
Null tail marks are acceptable. Canadian tail marks found in this data batch, should be cleaned up where possible.
FAA data error: Non American tail marks are truncated at 4 characters, for some reason. FAA SDR management has been notified.
NON-Canadian tail marks in CAWIS/WSDRS are stored in their entirety (under both WSDRS and the PTS subsystems).
Conversely, Canadian tail marks have the "C" prefix stripped off in all CAWIS subsystems.
In the FAA data, Canadian Tail marks arrive with a "C" prefix. Since CAWIS does not store the prefix for Canadian tail marks, something like 'CFBBB' needs to be ultimately stored as 'FBBB'.
Problem: For incoming Canadian tail marks, the final character has been truncated, and it would come in like so: CFBB
To repair Canadian tail mark data go to
https://wwwapps.tc.gc.ca/Saf-Sec-Sur/2/CCARCS-RIACC/
and do a partial tail mark lookup - or - a serial-number lookup to acquire the full tail mark and, acquire the final tail-mark character and swap it into the '?' value in the first statement below. There are normally only 1 or 2 records like this per data batch. Null out the tail mark if the aircraft cannot be found in CCARCS at all.
data sample - we remove the “C” and acquire the final character, if possible. The ones prefixed with “H” and “E” are unfixable, but we know that theyre truncated.
All other Non-American tail marks can be nulled out, though (optionally) Australian tail marks (those prefixed with "VH") can be acquired here:
https://www.casa.gov.au/aircraft-register
And repaired using the 2nd statement below. These are also missing the final character.
*/
--- REPAIR CANADIAN TAIL MARKS AFTER CCARCS LOOKUP -- IMPORTANT
SELECT SDR_NUMBER_NUM, AIRCRAFT_MODEL, AIRCRAFT_SERIAL_NUMBER, AIRCRAFT_MARK_ID,
' UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MARK_ID = ''' || SUBSTR(AIRCRAFT_MARK_ID,2,3) ||
'?'' WHERE AIRCRAFT_MODEL = ''' ||
AIRCRAFT_MODEL || ''' AND AIRCRAFT_SERIAL_NUMBER = ''' || AIRCRAFT_SERIAL_NUMBER || ''';' UPD1
FROM M70_SDR_TRANSIT WHERE AIRCRAFT_MARK_ID LIKE 'C%';
--- REPAIR AUSTRALIAN TAIL MARKS AFTER CASA LOOKUP -- OPTIONAL
SELECT SDR_NUMBER_NUM, AIRCRAFT_MODEL, AIRCRAFT_SERIAL_NUMBER, AIRCRAFT_MARK_ID,
' UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MARK_ID = ''' || AIRCRAFT_MARK_ID ||
'?'' WHERE AIRCRAFT_MODEL = ''' ||
AIRCRAFT_MODEL || ''' AND AIRCRAFT_SERIAL_NUMBER = ''' || AIRCRAFT_SERIAL_NUMBER || ''';' UPD1
FROM M70_SDR_TRANSIT WHERE AIRCRAFT_MARK_ID LIKE 'V%';
/*
All other Non-American tail marks -- We're nulling them out, because we know they've been truncated, and there's no one reliable place to look them up online.
This website is excellent for checking international registries, but does not have a serial number lookup.
*/
UPDATE M70_SDR_TRANSIT
SET AIRCRAFT_MARK_ID = NULL
WHERE SUBSTR(AIRCRAFT_MARK_ID,1,1) NOT IN ('N','F','G','V') ;
-- USA, CANADA, AUSTRALIA
commit;
/*