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