Breadcrumbs

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

:info:

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.

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

SQL
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

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

Table Join + Filter

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

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

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

Date Literals / Constants 

...  WHERE InvoiceDate >= {d'1990-01-01'}

Providex Documentation

Providex ODBC References

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