Versions Compared

Old Version 14

changes.mady.by.user Dan King

Saved on

compared with

New Version 15

changes.mady.by.user Dan King

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Cleaned up query syntax in 'Evaluate the Results' section.

...

  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);

-- View totals for each distinct 'exception_text' value
SELECT exception_text, Count(*) 
FROM bulkloader.address_extract
GROUP BY exception_text
ORDER BY exception_text;


  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 fields std_unit, street_segment, zone_id, address_id or  and 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 (std_unit IS NULL)
      AND (street_segment_id IS NULL)
      AND (zone_id IS NULL)
      AND (address_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 (std_unit IS NULL)
      AND (street_segment_id IS NULL)
      AND (zone_id IS NULL)
      AND (address_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);


  7. Evaluate records with non-NULL address_id values to count/view unit addresses that were added to the EAS.

    Code Block
    languagesql
    -- Count unit addresses added to the EAS:
SELECT COUNT(*)
FROM bulkloader.address_extract
WHERE NOT (address_id IS NULL);

-- View unit addresses added to the EAS:
SELECT *
FROM bulkloader.address_extract
WHERE NOT (address_id IS NULL);


Schema 'public'

Evaluate tables in the public schema to cross-check record counts from the bulkloader schema evaluation.

  1. Find the change_request_id of the most recent bulk load.

    Code Block
    languagesql
    -- Get the change_request_id of the most recent bulk load:
SELECT change_request_id
FROM public.change_requests
WHERE requestor_comment LIKE 'bulk load change request'
ORDER BY change_request_id DESC
LIMIT 1;

-- Make note of this id for the queries in the subsequent steps.


  2. Count and view new base addresses associated with the change request.

    Code Block
    languagesql
    -- Count new base addresses
SELECT COUNT (*)
FROM public.addresses
WHERE activate_change_request_id = change_request_id_goes_here
AND unit_num IS NULL;

-- View new base addresses
SELECT *
FROM public.addresses
WHERE activate_change_request_id = change_request_id_goes_here
AND unit_num IS NULL.;


  3. Count and view new unit addresses associated with the change request.

    Code Block
    languagesql
    --- Count new unit addresses
SELECT COUNT (*)
FROM public.addresses
WHERE activate_change_request_id = change_request_id_goes_here
AND NOT unit_num IS NULL;

-- View new unit addresses
SELECT *
FROM public.addresses
WHERE activate_change_request_id = change_request_id_goes_here
AND NOT unit_num IS NULL;


  4. Count and view all new addresses associated with the change request.

    Code Block
    languagesql
    -- Count all new addresses
SELECT COUNT (*)
FROM public.addresses
WHERE activate_change_request_id = change_request_id_goes_here;

-- View all new addresses
SELECT COUNT (*)
FROM public.addresses
WHERE activate_change_request_id = change_request_id_goes_here;


Free space after large Bulk Loader batches

...