Versions Compared

Old Version 8

changes.mady.by.user Dan King

Saved on

compared with

New Version 9

changes.mady.by.user Dan King

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Added query to count how many base records were not already in the EAS.

...

  1. 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
    languagesql
    -- 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);


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

    Code Block
    languagesql
    -- 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);


  3. 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.

  4. 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
    languagesql
    -- Count records where block and lot were provided but no address_x_parcel was assigned:
SELECT COUNT(*) FROM bulkloader.address_extract
WHERE (NOT (block IS NULL) OR NOT (block IS NULL))
AND address_x_parcel_id IS NULL;


  5. 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
    languagesql
    -- 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);


  6. Base addresses that were submitted by the Bulk Loader and were not already in the EAS are indicated by values in the street_segment_id field.

    Code Block
    languagesql
    -- Count base addresses that were not already in the EAS:
SELECT COUNT(*)
FROM bulkloader.address_extract
WHERE NOT (street_segment_id IS NULL)

-- View base addresses that were not already in the EAS:
SELECT *
FROM bulkloader.address_extract
WHERE NOT (street_segment_id IS NULL)



Free space after large Bulk Loader batches

...