1 Reply Latest reply: Nov 22, 2016 12:24 PM by Sarah Stefancies RSS

    Text() function on fields with leading zeros causing bad associations

    Sarah Stefancies

      I have a problem with one field across multiple tables: Cust-no.  In the database, this field is a varchar array so there are often two separate customers that are numerically equivalent.

       

      Current example: 00020047 and 20047

       

      These two customers have completely different profiles and associations with other tables.  I’ll keep this example simple and only explain the problems I’m having associating my customer table with my invoice table.

       

      Without making any adjustments to the LOAD script, all of the invoices are doubled, one for each customer.  Each customer is assigned the same Cust-no: 00020047. 20047 disappears.  This is problematic across all tables as each invoice in this scenario shows as having two different customers:

      table1.png

       

       

       

      I’ve tried to resolve this using text() in the load script.  I put it in both tables, customer only, and invoice only with the following results:

      Both tables:

      Invoice table > text("Invoice.Cust-no") AS "Cust-no",  (this loads from a qualified QVD)

      Customer table > text("Cust-no") AS "Cust-no",

       

      This appears to work at first, creating two separate customers with two different Cust-nos.

      However, the association fails.  All the invoices for 20047 belong to 00020047.

      This is inaccurate as 20047 is the Cust-no on many of the Document numbers.  20047 shows as having no invoices.

       

      table2.png

       

       

      Using the text() function on only the invoice table yields the same results.

      Using text() on only the customer table breaks the association completely.  Both 20047 and 00020047 have no invoices associated with them.

       

       

      Is there anything I can do in the select statement or some other function that I could use in the preceding load to force the Cust-no the be a string without breaking the association to other tables?