Skip to main content
Skip table of contents

Providex SQL Syntax

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

See also Sage 100 Technical Details

When possible use the T_SQL column on the ETL+ Design Page instead of the ETL+ Extract SQL Statement panel.

This page is only for customizing SQL on the ETL+ Extract SQL Statement Panel.

SQL Syntax for the PxPlus/Providex SQL ODBC Driver

Providex SQL Syntax Tips

This section is only for troubleshooting and/or optimizing the SQL in the ETL+ Extract SQL Statement Panel. See Sage 100 Providex Dates for processing problematic dates.

The following are snippets of popular SQL statements for ETL+ Extract SQL Statement Panel customizations:

ETL+Replace using Parent Date Filters

AR Invoice History

SQL
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

SQL
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)

SQL
{fn convert(OrderDate,SQL_VARCHAR)} as OrderDate

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

Related Pages

Providex Documentation

Reading PxPlus Language Reference Documentation

  1. Open http://manual.pvxplus.com/

  2. Select PxPlus SQL ODBC > Using the PxPlus SQL ODBC Driver

  3. Select https://manual.pvxplus.com/PXPLUS/odbc/using_odbc_driver/scalar_functions.htm for String, Numeric, Time and Date or System functions

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.