JJSystems
asked on
SQL0338 Error received on query
I have an application executing this query that used to work in iSeries O/S V5R4, but no longer works in V7R1. I understand IBM made a change to the way they handle joins in versions 6.1 and above, but can't figure out a way to get around the error. Here is the query:
Select H.PONumber, H.CreationDate, H.BuyerID, H.VendorID,
H.LDC, V.DBVen_Vendor_VName, Coalesce(DBVLo_Addr1,
DBVen_Addr1) as DBVen_Addr1, Coalesce(DBVLo_Addr2,
DBVen_Addr2) as DBVen_Addr2, Coalesce(DBVLo_Addr3,
DBVen_Addr3) as DBVen_Addr3, Coalesce(DBVLo_Addr4,
DBVen_Addr4) as DBVen_Addr4, Coalesce(DBVLo_City_Addr5,
DBVen_City_Addr5) as DBVen_City_Addr5,
Coalesce(DBVLo_State_Prov, DBVen_State_Prov) as
DBVen_State_Prov, Coalesce(DBVLo_Postal_Code ,
DBVen_Postal_Code) as DBVen_Postal_Code, S.StatusDescription
From PMFPOHeader H, PMFPODetails D, PMFPORevision R,
PMFPOStatus S, DBAPVEN V Left Outer Join DBAPVLo VL On
VL.DBVLo_Vendor = V.DBVEN_VENDOR and VL.DBVLo_Location_Code
= H.VendorLocationCode Where H.PONumber = 1000 and D.PONumber
= 1000 and H.VendorID = V.DBVEN_VENDOR and S.StatusID =
H.Status Group By H.PONumber, H.CreationDate, H.BuyerID,
H.VendorID, H.LDC, V.DBVEN_VENDOR_VNAME, VL.DBVLo_ADDR1,
VL.DBVLo_ADDR2, VL.DBVLo_ADDR3, VL.DBVLo_Addr4,
VL.DBVLo_City_Addr5, VL.DBVLo_State_Prov,
VL.DBVLo_Postal_Code, DBVen_ADDR1, DBVen_ADDR2, DBVen_ADDR3,
DBVen_Addr4, DBVen_City_Addr5, DBVen_State_Prov,
DBVen_Postal_Code, S.StatusDescription
Ideas?
Select H.PONumber, H.CreationDate, H.BuyerID, H.VendorID,
H.LDC, V.DBVen_Vendor_VName, Coalesce(DBVLo_Addr1,
DBVen_Addr1) as DBVen_Addr1, Coalesce(DBVLo_Addr2,
DBVen_Addr2) as DBVen_Addr2, Coalesce(DBVLo_Addr3,
DBVen_Addr3) as DBVen_Addr3, Coalesce(DBVLo_Addr4,
DBVen_Addr4) as DBVen_Addr4, Coalesce(DBVLo_City_Addr5,
DBVen_City_Addr5) as DBVen_City_Addr5,
Coalesce(DBVLo_State_Prov,
DBVen_State_Prov, Coalesce(DBVLo_Postal_Code
DBVen_Postal_Code) as DBVen_Postal_Code, S.StatusDescription
From PMFPOHeader H, PMFPODetails D, PMFPORevision R,
PMFPOStatus S, DBAPVEN V Left Outer Join DBAPVLo VL On
VL.DBVLo_Vendor = V.DBVEN_VENDOR and VL.DBVLo_Location_Code
= H.VendorLocationCode Where H.PONumber = 1000 and D.PONumber
= 1000 and H.VendorID = V.DBVEN_VENDOR and S.StatusID =
H.Status Group By H.PONumber, H.CreationDate, H.BuyerID,
H.VendorID, H.LDC, V.DBVEN_VENDOR_VNAME, VL.DBVLo_ADDR1,
VL.DBVLo_ADDR2, VL.DBVLo_ADDR3, VL.DBVLo_Addr4,
VL.DBVLo_City_Addr5, VL.DBVLo_State_Prov,
VL.DBVLo_Postal_Code, DBVen_ADDR1, DBVen_ADDR2, DBVen_ADDR3,
DBVen_Addr4, DBVen_City_Addr5, DBVen_State_Prov,
DBVen_Postal_Code, S.StatusDescription
Ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you!
old-style = joins using commas
new-style = explicitly using JOIN keyword
convert all your old-style joins to new-style joins and you should be ok.
HTH,
DaveSlash