Page Comparison

...

Evaluate the results of the bulk-load by opening the database and inspecting the output table bulkloader.address_extract for values in the field exception_text

Robert Gaston (Unlicensed) 21 Jun 2017 Verify the intent, and then revise the description if necessary, of #4 below.

Addresses that were successfully bulk-loaded will have a date in the field load_tms and a NULL value in the field exception_text.

Code Block

language	sql

-- Count records successfully bulk-loaded:
SELECT COUNT(*)
FROM bulkloader.address_extract
WHERE NOT(load_tms IS NULL) AND (exception_text IS NULL);

-- View records successfully bulk-loaded:
SELECT *
FROM bulkloader.address_extract
WHERE NOT(load_tms IS NULL) AND (exception_text IS NULL);

The presence of exception text indicates a problem with the source data.

Code Block

language	sql

-- Count records with exception:
SELECT COUNT(*)
FROM bulkloader.address_extract
WHERE NOT(exception_text IS NULL);

-- View records with exception:
SELECT *
FROM bulkloader.address_extract
WHERE NOT(exception_text IS NULL);

To rectify bulk-load exceptions, do not correct any exception records in the output table bulkloader.address_extract. Instead, correct the corresponding records in the source data and then re-run the Bulk Loader.

If block and lot values were included in the input data, a value should be found in the field address_x_parcel_id. Run the query below to find any records that had block and lot input values but did not get assigned a value for address_x_parcel_id.

Code Block

language	sql

SELECT-- *Count FROMrecords   bulkloader.address_extract
WHERE  NOT(address_x_parcel_id IS NULL); -- Blockwhere block and lot values were includedprovided inbut the input data.
no address_x_parcel was assigned:
SELECT COUNT(*) FROM   bulkloader.address_extract
WHERE (NOT (address_x_parcel_idblock IS NULL); --OR BlockNOT and(block lotIS valuesNULL))
were *not* included in the input dataAND address_x_parcel_id IS NULL;

Base addresses that were submitted by the Bulk Loader but were already in the EAS are indicated by values in the fields address_base_id and load_tms, and NULL values in the fields street_segment, zone_id or exception_text.

Code Block

language	sql

-- Count base addresses that were already in the EAS:
SELECT COUNT(*)
FROM bulkloader.address_extract
WHERE NOT(address_base_id IS NULL)
      AND NOT(load_tms IS NULL)
      AND (street_segment_id IS NULL)
      AND (zone_id IS NULL)
      AND (exception_text IS NULL);

-- View base addresses that were already in the EAS:
SELECT *
FROM bulkloader.address_extract
WHERE NOT(address_base_id IS NULL)
      AND NOT(load_tms IS NULL)
      AND (street_segment_id IS NULL)
      AND (zone_id IS NULL)
      AND (exception_text IS NULL);

...

Versions Compared

Old Version 7

New Version 8

Key