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

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

...
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

Dates and Date Fields

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

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

• See 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

• 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

• 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/scalar_functions.htm

    • Time and Date Functions

  • https://manual.pvxplus.com/PXPLUS/odbc/using_odbc_driver/example_sql.htm – SQL examples

• Providex ODBC Reference

• https://manual.pvxplus.com/PXPLUS/odbc/using_odbc_driver/sql_syntax_table.htm

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