Providex SQL Syntax
SQL Syntax for the ETL+ Extract SQL Statement panel with data (especially dates) from Sage 100 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.
See Sage 100 Providex Dates for processing problematic dates.
– Guide to finding SQL documentation in this reference manual.
http://manual.pvxplus.com – Link to the reference manual itself
Sage 100 Providex Dates – How to process ‘bad’ dates
Providex SQL Examples
See more SQL examples at: Sage 100 Providex SQL Syntax for ETL+ Extract SQL Statement panel,
https://manual.pvxplus.com/PXPLUS/odbc/using_odbc_driver/example_sql.htm (Sage documentation)
Examples of SQL statements that 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)
...
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 }Misc Examples
-- Examples to remove rows with bad date values
WHERE PostingDate >={d'2018-01-01'}
WHERE InvoiceDate >= {d'1990-01-01'} and InvoiceDueDate >= {d'1990-01-01'}
-- AP Aging
FROM
{IJ AP_OpenInvoice D INNER JOIN
AP_Vendor V ON D.VendorNo = V.VendorNo }
WHERE (D.Balance <> 0)
FROM AP_InvoiceHistoryHeader
WHERE InvoiceDate >= {d'1990-01-01'} and
InvoiceDueDate >= {d'1990-01-01'} and
TransactionDate >= {d'1990-01-01'}
-- AR Aging
FROM
{ IJ ( AR_Salesperson S INNER JOIN AR_OpenInvoice D ON S.SalespersonDivisionNo
= D.SalespersonDivisionNo AND S.SalespersonNo = D.SalespersonNo ) INNER JOIN
AR_Customer C ON D.ARDivisionNo = C.ARDivisionNo AND D.CustomerNo =
C.CustomerNo }
FROM AR_InvoiceHistoryHeader
WHERE InvoiceDate >= {d'1990-01-01'} and
InvoiceDueDate >= {d'1990-01-01'} and
TransactionDate >= {d'1990-01-01'} and
OrderDate >= {d'1990-01-01'}
--AR Inv Hist Header & Detail:
D.WarehouseCode,
H.InvoiceDate AS InvoiceDate
FROM
{ IJ AR_InvoiceHistoryHeader H INNER JOIN AR_InvoiceHistoryDetail D ON
H.HeaderSeqNo = D.HeaderSeqNo AND H.InvoiceNo = D.InvoiceNo }
WHERE H.InvoiceDate>={d'2018-01-01'}
-- PO Header & Detail
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 }
-- SO Header & Detail
FROM
{IJ SO_SalesOrderDetail D INNER JOIN
SO_SalesOrderHeader H ON D.SalesOrderNo = H.SalesOrderNo }Dates and Date Fields
Converting Date to String (to deal with dirty data, invalid dates)
... {fn convert(OrderDate,SQL_VARCHAR)} as OrderDateSee also Sage 100 Providex Dates
Date Literals / Constants
... WHERE InvoiceDate >= {d'1990-01-01'}Related Pages
Sage 100 Pre-mapped Data Warehouse Tables – Pre-mapped ETL+ tables available for Sage 100
ETL+ Load Replace with Date-time fields – special caution for Sage 100 users.
Providex Documentation
Providex ODBC Syntax Functions and Data Types from http://manual.pvxplus.com
https://manual.pvxplus.com/PXPLUS/odbc/using_odbc_driver/sql_syntax_table.htm
Providex ODBC References
Reading PxPlus™ Language Reference Documentation
Select PxPlus SQL ODBC > Using the PxPlus SQL ODBC Driver
Select https://manual.pvxplus.com/PXPLUS/odbc/using_odbc_driver/scalar_functions.htm for String, Numeric, Time and Date or System functions