Providex SQL Syntax

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

See also Sage 100 Technical Details

SQL Syntax for the PxPlus/Providex SQL ODBC Driver

• Sage 100 PxPlus Reference Manual / Providex – 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 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

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

Related Pages

• https://dataself.atlassian.net/wiki/pages/resumedraft.action?draftId=2094923777&draftShareId=6e5e7959-4f5b-46ae-ae0b-d431f2abc93c

  • Sage 100 Providex Dates

• Source Systems and IT

  • Sage 100 Technical Details

    • Sage 100 Providex ODBC DSN Setup - Summarized

    • Sage 100 Providex ODBC DSN Setup - Detailed

• ETL+ for Sage 100 MS SQL Server

• DataSelf Analytics For Sage 100

Providex Documentation

• 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

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