PxPlus User Forum

Main Board => Discussions => ODBC => Topic started by: mm_coder on October 02, 2021, 02:10:07 PM

Title: Powershel ODBC query Lexical identifier error
Post by: mm_coder on October 02, 2021, 02:10:07 PM

I'm writing a powershell script to run the following query, but I get the lexical identifier error.

Original SQL script:
SELECT SO_SalesOrderDetail.SalesOrderNo, SO_SalesOrderHeader.OrderType, SO_SalesOrderHeader.OrderStatus, SO_SalesOrderDetail.LineKey, SO_SalesOrderDetail.ItemCode, SO_SalesOrderDetail.ItemCodeDesc, SO_SalesOrderDetail.PromiseDate, SO_SalesOrderDetail.PurchaseOrderNo, IM_ItemWarehouse.QuantityOnHand, IM_ItemWarehouse.QuantityOnPurchaseOrder, IM_ItemWarehouse.QuantityOnSalesOrder, IM_ItemWarehouse.WarehouseCode, IM_ItemWarehouse.BinLocation
FROM (SO_SalesOrderDetail INNER JOIN SO_SalesOrderHeader ON SO_SalesOrderDetail.SalesOrderNo = SO_SalesOrderHeader.SalesOrderNo) INNER JOIN IM_ItemWarehouse ON SO_SalesOrderDetail.ItemCode = IM_ItemWarehouse.ItemCode
WHERE (((SO_SalesOrderDetail.ItemCode)="S-209220"));



My attempt at adding the lexical correct version using oj:

"SELECT SO_SalesOrderDetail.SalesOrderNo, SO_SalesOrderHeader.OrderType, SO_SalesOrderHeader.OrderStatus, SO_SalesOrderDetail.LineKey, SO_SalesOrderDetail.ItemCode, SO_SalesOrderDetail.ItemCodeDesc, SO_SalesOrderDetail.PromiseDate,
 SO_SalesOrderDetail.PurchaseOrderNo, IM_ItemWarehouse.QuantityOnHand, IM_ItemWarehouse.QuantityOnPurchaseOrder, IM_ItemWarehouse.QuantityOnSalesOrder, IM_ItemWarehouse.WarehouseCode, IM_ItemWarehouse.BinLocation
FROM (SO_SalesOrderDetail {oj SO_SalesOrderHeader ON SO_SalesOrderDetail.SalesOrderNo = SO_SalesOrderHeader.SalesOrderNo)} {oj LEFT INNER JOIN IM_ItemWarehouse ON SO_SalesOrderDetail.ItemCode = IM_ItemWarehouse.ItemCode}
WHERE (((SO_SalesOrderDetail.ItemCode)='S-209220'));"


Best regards

Title: Re: Powershel ODBC query Lexical identifier error
Post by: Devon Austen on October 04, 2021, 08:16:11 AM

We have documentation that describes the different JOINS including 3+ table joins and it includes examples.

https://manual.pvxplus.com/?odbc/using_odbc_driver.htm

Also note that as of PxPlus SQL ODBC version 6 the {oj....} is no longer required to do joins you can use the simple top syntax to accomplish your query. This is also in the doc with examples.