PxPlus User Forum
Main Board => Discussions => ODBC => Topic started by: eldonfsr on December 30, 2022, 07:11:38 PM
-
Hi i need some body explain a lite bit about join left and right i trying to execute select query i read manuals and supost syntax is like that..
select * from { OJ IM1_inventoryMasterFile full outer join AP1_VendorMaster left outer join IMA_ProductLine on IMA_ProductLine.ProductLineCode=IM1_inventoryMasterFile.productLine on AP1_VendorMaster.VendorNumber=IM1_inventoryMasterFile.PrimaryVendorNumber } where IM1_inventoryMasterFile.itemnumber=:pprod
what i need is get inventorymaterfile get all information and link Ima_productLineCode to get description of product line and from ap1_vendorMaster get vendor name , but send me error productline column not found.. this column is on inventorymasterfile..... what is rigth syntax to get access the imnformation...
-
Some descriptions of the different joins are found here: https://manual.pvxplus.com/?odbc/using_odbc_driver.htm
Some examples are foudn here: https://manual.pvxplus.com/?odbc/using_odbc_driver/example_sql.htm
My guess is this is what you want.
select * from { OJ (IM1_inventoryMasterFile left outer join IMA_ProductLine on IMA_ProductLine.ProductLineCode=IM1_inventoryMasterFile.productLine) left outer join AP1_VendorMaster on AP1_VendorMaster.VendorNumber=IM1_inventoryMasterFile.PrimaryVendorNumber } where IM1_inventoryMasterFile.itemnumber=:pprod
Not that if you are using the PxPlus SQL ODBC driver version 6 or higher you can simplify the syntax to remove the { oJ }
select * from (IM1_inventoryMasterFile left outer join IMA_ProductLine on IMA_ProductLine.ProductLineCode=IM1_inventoryMasterFile.productLine) left outer join AP1_VendorMaster on AP1_VendorMaster.VendorNumber=IM1_inventoryMasterFile.PrimaryVendorNumber where IM1_inventoryMasterFile.itemnumber=:pprod