STEP 11 Text field Cleanups

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

The Z980_Term_conversion table serves a multitude of purposes.

Its primary reason is to enforce MANUFACTURER spellings throughout CAWIS and prevent duplication thru auto-correction.

By defining various TERM_TYPE_Codes (ttc), we use this table for further data verification and/or purification.

A full list of term types and descriptions can be had at : TR32_TERM_TYPE

Open

So Term_type_cd = SDBAD is a list of BAD/UNWANTED/UNNECESSARY data entries identified thru many years of data review that we use to clear out trash values from various Text fields as seen below.

Sample values

Open

The word, "UNKNOWN" is an agreed upon (with TC SDRS management) default value placed into NULL serial numbers, and part data fields because the CAWIS/WSDRS page demands these data elements as mandatory fields. We fill them to prevent this error (even though - again - at this point TC users cannot go into edit mode on a non-Canadian SDR)

*/

UPDATE M70_SDR_TRANSIT  SET AIRCRAFT_MARK_ID        = NULL

WHERE AIRCRAFT_MARK_ID        = CARRIER_CD ;  --- common FAA data entry error

UPDATE M70_SDR_TRANSIT  SET AIRCRAFT_MARK_ID        = NULL

WHERE AIRCRAFT_MARK_ID        IN (SELECT Z980.FROM_TXT FROM Z980_TERM_CONVERSION Z980

                                  WHERE Z980.TERM_TYPE_CD = 'SDBAD') ;

UPDATE M70_SDR_TRANSIT  SET AIRCRAFT_SERIAL_NUMBER  = 'UNKNOWN'

WHERE AIRCRAFT_SERIAL_NUMBER  IN (SELECT Z980.FROM_TXT FROM Z980_TERM_CONVERSION Z980

                                  WHERE Z980.TERM_TYPE_CD = 'SDBAD') ;

UPDATE M70_SDR_TRANSIT  SET ENGINE_SERIAL_NUMBER    = 'UNKNOWN'

WHERE ENGINE_SERIAL_NUMBER    IN (SELECT Z980.FROM_TXT FROM Z980_TERM_CONVERSION Z980

                                  WHERE Z980.TERM_TYPE_CD = 'SDBAD') ;

UPDATE M70_SDR_TRANSIT  SET PROPELLER_SERIAL_NUMBER = 'UNKNOWN'

WHERE PROPELLER_SERIAL_NUMBER IN (SELECT Z980.FROM_TXT FROM Z980_TERM_CONVERSION Z980

                                  WHERE Z980.TERM_TYPE_CD = 'SDBAD') ;

UPDATE M70_SDR_TRANSIT  SET COMPONENT_SERIAL_NUMBER = 'UNKNOWN'

WHERE COMPONENT_SERIAL_NUMBER IN (SELECT Z980.FROM_TXT FROM Z980_TERM_CONVERSION Z980

                                  WHERE Z980.TERM_TYPE_CD = 'SDBAD') ;

UPDATE M70_SDR_TRANSIT  SET COMPONENT_MANUFACTURER  = 'MISC EQUIPMENT'

WHERE COMPONENT_MANUFACTURER  IN (SELECT Z980.FROM_TXT FROM Z980_TERM_CONVERSION Z980

                                  WHERE Z980.TERM_TYPE_CD = 'SDBAD') ;

UPDATE M70_SDR_TRANSIT  SET COMPONENT_NAME          = NULL

WHERE COMPONENT_NAME          IN (SELECT Z980.FROM_TXT FROM Z980_TERM_CONVERSION Z980

                                  WHERE Z980.TERM_TYPE_CD = 'SDBAD') ;

UPDATE M70_SDR_TRANSIT  SET PART_NAME               = 'UNKNOWN'

WHERE PART_NAME               IN (SELECT Z980.FROM_TXT FROM Z980_TERM_CONVERSION Z980

                                  WHERE Z980.TERM_TYPE_CD = 'SDBAD') ;

UPDATE M70_SDR_TRANSIT  SET PART_NUMBER             = 'UNKNOWN'

WHERE PART_NUMBER             IN (SELECT Z980.FROM_TXT FROM Z980_TERM_CONVERSION Z980

                                  WHERE Z980.TERM_TYPE_CD = 'SDBAD') ;

UPDATE M70_SDR_TRANSIT  SET PART_CONDITION          = 'UNKNOWN'

WHERE PART_CONDITION          IN (SELECT Z980.FROM_TXT FROM Z980_TERM_CONVERSION Z980

                                  WHERE Z980.TERM_TYPE_CD = 'SDBAD') ;

UPDATE M70_SDR_TRANSIT  SET PART_LOCATION           = NULL

WHERE PART_LOCATION           IN (SELECT Z980.FROM_TXT FROM Z980_TERM_CONVERSION Z980

                                  WHERE Z980.TERM_TYPE_CD = 'SDBAD') ;

-- DOUBLE CHECK THAT THESE FIELDS ARE DATA-COMPRESSED or TRIMMED

UPDATE M70_SDR_TRANSIT  SET AIRCRAFT_MANUFACTURER   = DATA_COMPRESSION(AIRCRAFT_MANUFACTURER) ; 

UPDATE M70_SDR_TRANSIT  SET AIRCRAFT_MODEL          = DATA_COMPRESSION(AIRCRAFT_MODEL) ; 

UPDATE M70_SDR_TRANSIT  SET AIRCRAFT_SERIAL_NUMBER  = DATA_COMPRESSION(AIRCRAFT_SERIAL_NUMBER) ; 

UPDATE M70_SDR_TRANSIT  SET ENGINE_MANUFACTURER     = DATA_COMPRESSION(ENGINE_MANUFACTURER) ; 

UPDATE M70_SDR_TRANSIT  SET ENGINE_MODEL            = DATA_COMPRESSION(ENGINE_MODEL) ; 

UPDATE M70_SDR_TRANSIT  SET ENGINE_SERIAL_NUMBER    = DATA_COMPRESSION(ENGINE_SERIAL_NUMBER) ; 

UPDATE M70_SDR_TRANSIT  SET PROPELLER_MANUFACTURER  = DATA_COMPRESSION(PROPELLER_MANUFACTURER) ; 

UPDATE M70_SDR_TRANSIT  SET PROPELLER_MODEL         = DATA_COMPRESSION(PROPELLER_MODEL);

UPDATE M70_SDR_TRANSIT  SET PROPELLER_SERIAL_NUMBER = DATA_COMPRESSION(PROPELLER_SERIAL_NUMBER) ; 

UPDATE M70_SDR_TRANSIT  SET COMPONENT_MANUFACTURER  = DATA_COMPRESSION(COMPONENT_MANUFACTURER) ; 

UPDATE M70_SDR_TRANSIT  SET COMPONENT_MODEL         = DATA_COMPRESSION(COMPONENT_MODEL) ; 

UPDATE M70_SDR_TRANSIT  SET COMPONENT_SERIAL_NUMBER = DATA_COMPRESSION(COMPONENT_SERIAL_NUMBER) ; 

UPDATE M70_SDR_TRANSIT  SET PART_NUMBER             = DATA_COMPRESSION(PART_NUMBER) ;

COMMIT;

UPDATE M70_SDR_TRANSIT  SET PART_NAME               = TRIM(PART_NAME) ; 

UPDATE M70_SDR_TRANSIT  SET PART_CONDITION          = TRIM(PART_CONDITION) ; 

UPDATE M70_SDR_TRANSIT  SET PART_LOCATION           = TRIM(PART_LOCATION) ;  

UPDATE M70_SDR_TRANSIT  SET PART_NAME      = 'UNKNOWN' WHERE TRIM(PART_NAME)      IS NULL ;

UPDATE M70_SDR_TRANSIT  SET PART_CONDITION = 'UNKNOWN' WHERE TRIM(PART_CONDITION) IS NULL ;

UPDATE M70_SDR_TRANSIT  SET PART_LOCATION  = 'UNKNOWN' WHERE TRIM(PART_LOCATION)  IS NULL ;

UPDATE M70_SDR_TRANSIT  SET PART_NUMBER    = 'UNKNOWN' WHERE TRIM(PART_NUMBER)    IS NULL ;

COMMIT;

--- ENSURE THE DATE FIELDS ARE POPULATED

UPDATE M70_SDR_TRANSIT  SET ENTRY_DATE = SYSDATE         WHERE  ENTRY_DATE     IS NULL ;

UPDATE M70_SDR_TRANSIT  SET LAST_UPDATE = ENTRY_DATE     WHERE  LAST_UPDATE    IS NULL ;

UPDATE M70_SDR_TRANSIT  SET OCCURENCE_DATE = ENTRY_DATE  WHERE  OCCURENCE_DATE IS NULL ;

COMMIT;