Page Comparison

...

• 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:

  • See Sage 100 Providex Dates

• 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

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

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)

{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

• Providex ODBC Syntax Functions and Data Types

• Providex ODBC Reference

Premapped ETL+ Tables

Sage 100 Pre-mapped Data Warehouse Tables

...

• Providex ODBC Syntax Functions and Data Types from http://manual.pvxplus.com

  • https://manual.pvxplus.com/PXPLUS/odbc/using_odbc_driver/scalar_functions.htm

    • Time and Date Functions

• Providex ODBC Reference

Providex ODBC References

• Providex ODBC Syntax Functions and Data Types

• Providex ODBC Reference

Reading PxPlus^™ Language Reference Documentation

...