AR: Fix Leading/Trailing Spaces in Jobs

When a job gets a trailing or leading space it leads to many strange behaviors in Keystone. Interbase makes this a very tricky issue to ID and to fix. You can not rely on string length and comparing the RTRIM or LTRIM values to the original field value. If you Append a character before and after the field and the same to the trimmed value you can do a comparison.

The following will find jobs with spaces in the invoice table:

select 'inv', '>'||JOB_PO_LOC||'<' "Before",'>'||ltrim(rtrim(JOB_PO_LOC))||'<' "After",j.cust_no,j.job_po_loc,count(*) from artinv j
where '>'||JOB_PO_LOC||'<' <> '>'||ltrim(rtrim(JOB_PO_LOC))||'<' group by j.cust_no,j.job_po_loc

This is an example of fixing the job name in the Job Header table:

update artjobhd
set job_po_loc=ltrim(rtrim(JOB_PO_LOC))
where '>'||JOB_PO_LOC||'<' <> '>'||ltrim(rtrim(JOB_PO_LOC))||'<';

Processing all tables at once

To make it easy - here are all the tables in one query...

Identify

select 'inv' "tbl", '>'||JOB_PO_LOC||'<' "Before",'>'||ltrim(rtrim(JOB_PO_LOC))||'<' "After",j.cust_no,j.job_po_loc,count(*) RecordCount,Max(invoice_date) LastDate from artinv j
where '>'||JOB_PO_LOC||'<' <> '>'||ltrim(rtrim(JOB_PO_LOC))||'<' group by j.cust_no,j.job_po_loc
union all
select 'cnt' , '>'||entity_sub_id||'<' "Before",'>'||ltrim(rtrim(entity_sub_id))||'<' "After",j.entity_id "CUST_NO",j.entity_sub_id "JOB_PO_LOC",count(*),cast(max(last_change_datetime) as date) from cctcont j
where '>'||entity_sub_id||'<' <> '>'||ltrim(rtrim(entity_sub_id))||'<' group by j.entity_id,j.entity_sub_id
union all
select 'ord', '>'||JOB_PO_LOC||'<' "Before",'>'||ltrim(rtrim(JOB_PO_LOC))||'<' "After",j.cust_no,j.job_po_loc,count(*),max(delivery_date) from artordhd j
where '>'||JOB_PO_LOC||'<' <> '>'||ltrim(rtrim(JOB_PO_LOC))||'<' group by j.cust_no,j.job_po_loc
union all
select 'job', '>'||JOB_PO_LOC||'<' "Before",'>'||ltrim(rtrim(JOB_PO_LOC))||'<' "After",j.cust_no,j.job_po_loc,count(*),cast(max(last_change_datetime) as date) from artjobhd j
where '>'||JOB_PO_LOC||'<' <> '>'||ltrim(rtrim(JOB_PO_LOC))||'<' group by j.cust_no,j.job_po_loc
union all
select 'jdt', '>'||JOB_PO_LOC||'<' "Before",'>'||ltrim(rtrim(JOB_PO_LOC))||'<' "After",j.cust_no,j.job_po_loc,count(*),cast(null as date) from artjobdt j
where '>'||JOB_PO_LOC||'<' <> '>'||ltrim(rtrim(JOB_PO_LOC))||'<' group by j.cust_no,j.job_po_loc
union all
select 'ptk' tbl, '>'||JOB_PO_LOC||'<' "Before",'>'||ltrim(rtrim(JOB_PO_LOC))||'<' "After",j.cust_no,j.job_po_loc,count(*),max(ticket_date) from artptkhd j
where '>'||JOB_PO_LOC||'<' <> '>'||ltrim(rtrim(JOB_PO_LOC))||'<' group by j.cust_no,j.job_po_loc
union all
select 'tck', '>'||JOB_PO_LOC||'<' "Before",'>'||ltrim(rtrim(JOB_PO_LOC))||'<' "After",j.cust_no,j.job_po_loc,count(*),max(ticket_date) from arttckhd j
where '>'||JOB_PO_LOC||'<' <> '>'||ltrim(rtrim(JOB_PO_LOC))||'<' group by j.cust_no,j.job_po_loc
union all
select 'sls', '>'||JOB_PO_LOC||'<' "Before",'>'||ltrim(rtrim(JOB_PO_LOC))||'<' "After",j.cust_no,j.job_po_loc,count(*),max(ticket_date) from artslshd j
where '>'||JOB_PO_LOC||'<' <> '>'||ltrim(rtrim(JOB_PO_LOC))||'<' group by j.cust_no,j.job_po_loc
union all
select 'qte', '>'||JOB_PO_LOC||'<' "Before",'>'||ltrim(rtrim(JOB_PO_LOC))||'<' "After",j.prospect_no,j.job_po_loc,count(*),cast(max(last_change_datetime) as date) from qttjobhd j
where '>'||JOB_PO_LOC||'<' <> '>'||ltrim(rtrim(JOB_PO_LOC))||'<' group by j.prospect_no,j.job_po_loc
union all
select 'qdt', '>'||JOB_PO_LOC||'<' "Before",'>'||ltrim(rtrim(JOB_PO_LOC))||'<' "After",j.prospect_no,j.job_po_loc,count(*),cast(null as date) from qttjobdt j
where '>'||JOB_PO_LOC||'<' <> '>'||ltrim(rtrim(JOB_PO_LOC))||'<' group by j.prospect_no,j.job_po_loc

Identify Duplicate Jobs

Find Jobs that are duplicated - once with extra space and once without...

select 'job', '>'||JOB_PO_LOC||'<' "Before",'>'||ltrim(rtrim(JOB_PO_LOC))||'<' "After",j.cust_no,j.job_po_loc,count(*) from artjobhd j
where '>'||JOB_PO_LOC||'<' <> '>'||ltrim(rtrim(JOB_PO_LOC))||'<'
and exists (select 1 from artjobhd jj where jj.cust_no=j.cust_no and '>'||jj.JOB_PO_LOC||'<'='>'||ltrim(rtrim(j.JOB_PO_LOC))||'<' )
 group by j.cust_no,j.job_po_loc

These must be dealt with manually.

Update

Once duplicate jobs have been dealt with you can process this update query...

update artjobhd
set job_po_loc=ltrim(rtrim(JOB_PO_LOC))
where '>'||JOB_PO_LOC||'<' <> '>'||ltrim(rtrim(JOB_PO_LOC))||'<';
update artjobdt
set job_po_loc=ltrim(rtrim(JOB_PO_LOC))
where '>'||JOB_PO_LOC||'<' <> '>'||ltrim(rtrim(JOB_PO_LOC))||'<';
update artordhd
set job_po_loc=ltrim(rtrim(JOB_PO_LOC))
where '>'||JOB_PO_LOC||'<' <> '>'||ltrim(rtrim(JOB_PO_LOC))||'<';
update artinv
set job_po_loc=ltrim(rtrim(JOB_PO_LOC))
where '>'||JOB_PO_LOC||'<' <> '>'||ltrim(rtrim(JOB_PO_LOC))||'<';
update artslshd
set job_po_loc=ltrim(rtrim(JOB_PO_LOC))
where '>'||JOB_PO_LOC||'<' <> '>'||ltrim(rtrim(JOB_PO_LOC))||'<';
update arttckhd
set job_po_loc=ltrim(rtrim(JOB_PO_LOC))
where '>'||JOB_PO_LOC||'<' <> '>'||ltrim(rtrim(JOB_PO_LOC))||'<';
update artptkhd
set job_po_loc=ltrim(rtrim(JOB_PO_LOC))
where '>'||JOB_PO_LOC||'<' <> '>'||ltrim(rtrim(JOB_PO_LOC))||'<';
update qttjobhd
set job_po_loc=ltrim(rtrim(JOB_PO_LOC))
where '>'||JOB_PO_LOC||'<' <> '>'||ltrim(rtrim(JOB_PO_LOC))||'<';
update qttjobdt
set job_po_loc=ltrim(rtrim(JOB_PO_LOC))
where '>'||JOB_PO_LOC||'<' <> '>'||ltrim(rtrim(JOB_PO_LOC))||'<';
update cctcont
set entity_sub_id=ltrim(rtrim(entity_sub_id))
where '>'||entity_sub_id||'<' <> '>'||ltrim(rtrim(entity_sub_id))||'<';