...
Failed to load tables:
Check that the user assigned to DataSelf has reading rights to that particular data set.
Click
Back
from the Log page, on the ETL main page, select the failing table on the center panel, check that its SQL Statement on the right panel has correct information.
Issues loading Date columns with invalid values for SQL:
Tables with record count different than in Sage 100:
It might be just that new records have been populated in Sage 100 since the DataSelf data load. Reload the data. Click
Back
from the Log page, on the ETL main page, select the table with incorrect row cound on the center panel,right-click
the table on the center panel again ->Load Now
. This might take a while if the table has a large data set.The user assigned to DataSelf might have access to limited data sets, such as only data from one Division. Check the user’s security credentials.
Providex SQL Syntax Tips
This section is only for troubleshooting and/or optimizing the SQL in the ETL+ Extract SQL Statement Panel .
Customizing the ETL+ Extract SQL Statement Panel for extracting from Providex.
The following are snippets of popular SQL statements for ETL+ Extract SQL Statement Panel customizations:
ETL+Replace using Parent Date Filters
AR Invoice History
Code Block | ||
---|---|---|
| ||
H.InvoiceDate AS InvoiceDate
FROM
{ IJ AR_InvoiceHistoryHeader H INNER JOIN AR_InvoiceHistoryDetail D ON
H.HeaderSeqNo = D.HeaderSeqNo AND H.InvoiceNo = D.InvoiceNo } |
SO History Header & Detail
Code Block | ||
---|---|---|
| ||
H.OrderDate as OrderDate
FROM
{IJ SO_SalesOrderHistoryDetail D INNER JOIN
SO_SalesOrderHistoryHeader H ON D.SalesOrderNo = H.SalesOrderNo } |
Converting Date to String (to deal with dirty data, invalid dates)
Code Block | ||
---|---|---|
| ||
{fn convert(OrderDate,SQL_VARCHAR)} as OrderDate |
See also Sage 100 Providex Dates
Single Table Join + Table Prefix + Filter
FROM { IJ IM_ItemWarehouse D INNER JOIN CI_Item I ON D.ItemCode = I.ItemCode }
WHERE D.DocDate>={d'2018-01-01'}
Double Table Join
FROM { IJ (PO_PurchaseOrderDetail D INNER JOIN PO_PurchaseOrderHeader H ON D.PurchaseOrderNo = H.PurchaseOrderNo ) INNER JOIN CI_Item I ON D.ItemCode = I.ItemCode }
Providex ODBC References
Premapped ETL+ Tables
Sage 100 Pre-mapped Data Warehouse Tables
...
Providex ODBC References
Reading PxPlus™ Language Reference Documentation
...