Providex SQL Syntax

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

See also https://dataself.atlassian.net/wiki/spaces/DS/pages/1666482584

When possible use the T_SQL column on the https://dataself.atlassian.net/wiki/spaces/DS/pages/1637023747 instead of the ETL+ Extract SQL Statement panel.

This page is only for customizing SQL on the https://dataself.atlassian.net/wiki/spaces/DS/pages/1928527873.

See for processing problematic dates.

SQL Syntax for the PxPlus/Providex SQL ODBC Driver

Providex SQL Examples

Join & Extract a Date Field from “Parent” Table for Load Replace

AR Invoice History

SELECT 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

SELECT H.OrderDate as OrderDate FROM {IJ SO_SalesOrderHistoryDetail D INNER JOIN SO_SalesOrderHistoryHeader H ON D.SalesOrderNo = H.SalesOrderNo }

Table Join + Filter

SELECT D.DocDate 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 (JOIN 3 Tables)

Providex ODBC References

Dates and Date Fields

Converting Date to String (to deal with dirty data, invalid dates)

  • See also

Date Literals / Constants

Providex Documentation

Reading PxPlus Language Reference Documentation

  1. Open

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

  3. Select for String, Numeric, Time and Date or System functions