Solved

SQL0338 Error received on query

Posted on 2014-09-19
3
304 Views
Last Modified: 2014-09-19
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
Comment
Question by:JJSystems
  • 2
3 Comments
 
LVL 18

Expert Comment

by:daveslash
ID: 40332999
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
 
LVL 18

Accepted Solution

by:
daveslash earned 500 total points
ID: 40333045
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
 

Author Closing Comment

by:JJSystems
ID: 40333454
Thank you!
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question