Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

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?
0
JJSystems
Asked:
JJSystems
  • 2
1 Solution
 
Dave FordSoftware Developer / Database AdministratorCommented:
Ahh yes. I see it. You mixed "old-style" joins with "new style" joins.

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
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
Something like this should work:

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
  join PMFPODetails D
    on D.PONumber = H.PONumber
  cross join PMFPORevision R
  join PMFPOStatus S
    on S.StatusID = H.Status
  join DBAPVEN V 
    on H.VendorID = V.DBVEN_VENDOR
  Left Outer Join DBAPVLo VL
    On VL.DBVLo_Vendor = V.DBVEN_VENDOR
   and VL.DBVLo_Location_Code = H.VendorLocationCode
 Where H.PONumber = 1000
 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

Open in new window


I noticed that you didn't specify any join-criteria for table PMFPORevision, I assume you meant for it to be a cross-join.

I find it best to fully enter this century and abandon all old-style joins. They're much "clearer" and easier to code.

HTH,
DaveSlash
0
 
JJSystemsAuthor Commented:
Thank you!
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now