PxPlus User Forum

Twitter Twitter Twitter

Author Topic: Powershel ODBC query Lexical identifier error  (Read 841 times)

mm_coder

  • Member
  • **
  • Posts: 5
    • View Profile
Powershel ODBC query Lexical identifier error
« 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

Devon Austen

  • Administrator
  • Diamond Member
  • *****
  • Posts: 382
  • Don’t Panic
    • View Profile
    • PVX Plus Technologies
Re: Powershel ODBC query Lexical identifier error
« Reply #1 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.
Principal Software Engineer for PVX Plus Technologies LTD.