Providex SQL Syntax

SQL Syntax for use on the ETL+ Extract SQL Statement panel with data from Providex data sources.

SQL Syntax for the PxPlus/Providex SQL ODBC Driver

Sage 100 PxPlus Reference Manual / Providex – Guide to finding SQL documentation in this reference manual.
– Link to the reference manual itself
– How to process ‘bad’ dates

Providex SQL Syntax Tips

This section is only for troubleshooting and/or optimizing the SQL in the . See for processing problematic dates.

The following are snippets of popular SQL statements for 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

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

DataSelf Analytics For Sage 100

Providex Documentation

Reading PxPlus^™ Language Reference Documentation

Open
Select PxPlus SQL ODBC > Using the PxPlus SQL ODBC Driver
Select for String, Numeric, Time and Date or System functions

SQL Syntax for the PxPlus/Providex SQL ODBC Driver