Related to my previous post, I successfully created a small job to grab and insert a handful of Order/Invoice header fields from a sybase db into Sage 300 Orders. The select was simple using a DB Reader:

SELECT CUSTNO, INVNO, INVDTE, ORDTE, PONUM

FROM ARINVOI

WHERE CUSTONO LIKE 'A%' (I ADDED THIS WHERE CLAUSE TO TEST WITH A SMALLER DATASET)

In the source db, the Order/Invoice header and detail data is separated into two different tables like 300 but, being n00bish and relaying on the documentation, I assumed the approach was to pull the header and details together anyway in Iman and then use a map transform and hierarchy transform to organize the data.

(NOTE: If there is a better way to handle the integration of header/detail records, from a source db into a target db, I am all ears for a best practice. I recognize that since I have a job to insert the headers, I could just run a second job to insert the details, and then a third to ship all and invoice ... but I doubt this is the most efficient approach.)

So going with my plan, I modified the SQL statement as follows adding left join to grab the order/invoice details:

SELECT ARINVOI.CUSTNO, ARINVOI.INVNO, ARINVOI.INVDTE, ARINVOI.ORDTE, ARINVOI.PONUM, ARTRANS.ITEM, ARTRANS.ID, ARTRANS.UNITMS, ARTRANS.QTYSHP, ARTRANS.WEIGHT, ARTRANS.UNITPR, ARTRANS.REQ1

FROM ARINVOI

LEFT JOIN ARTRANS ON ARINVOI.INVNO = ARTRANS.INVNO

WHERE ARINVOI.CUSTNO LIKE 'A%'

Trying to Refresh and return a sample data-set, I am getting the following errors - note that running the query directly in Sybase returns the results just fine in 1.279 seconds:

These also appear to be the same errors I am receiving on my PO import job where I'm having issues with the mapping of the Vendor Number.