Report sends incorrect SQL statement - anyone experienced something similar

[jdesmm]

We copied the base V03B11P and R03B4201B A/R Details with Aging report some time ago and made custom modifications to it. It has been working properly in DEV, CRP, PROD, etc. We recently began testing update 6 in our SU environment and found that there is a problem with the report. The report is adding OR clauses to data selection that are not in data selection. For instance, if the user selects "Where Ordered By = 002", the SQL statement submitted by JDE to the server will read "Where SHORBY = '002' OR SHORBY = null" We have already tried re-creating versions and transferring report and business view specifications from PROD to SU, but we continue to see these results. In comparing the report between SU and PROD, one thing we noticed that we hadn't seen before was that the PROD SQL statement contained a field (ABMCU) that is not actually included in the business view. This seems really weird, even though it is not the same problem we're having in SU and not causing the users any problems with the report.

I am wondering if anyone has seen this type of thing happen before, and if so, I would like to know what causes it and how we can fix it. I am nearly to the point of thinking we need to re-copy the objects and re-apply our changes, but that will be an arduous task I would like to avoid. We're placing a call to JDE as well, but I don't expect much help from them given that the report & business view are now custom.

 

[cdawes]

Have you tried rebuilding the packages?

Also what service pack are you running? Try installing a newer service pack
on a FAT client only and run the UBE locally to see if the same results
occur.

Colin





Colin Dawes, Sr. Technical Consultant
Syntax.net
B733.1 to ERP 8.0
Oracle 8i/9i/SQL Server 2K, DB2

 

[Gergely_Pongracz]

Hi,
This "or null" type selection looks like and outer join... Check in the business view. I have quite bad experiences with these types - sometimes they work, sometimes they don't, and this is dependent on almost everything - SP level, Database platform, etc. If this is the situation the safest solutions is "programming" the outer join.
Regards:
Gergely Pongrácz
e-Best, Hungary

 

[jdesmm]

Found It

I ended up finding a SAR which explained the behavior we're witnessing, #5970284. The SAR has supposedly been in effect since B7332, so the real mystery for us now is why our report was *not* behaving that way in our non update 6 / sp 20 environment. I found other reports we've done with left outer joins, and they do behave as per the SAR in all environments. We're chalking it up to some oddity and trying to work around the OR clause with other data selection.

Just for everyone's information, here's what the SAR states:

Currently, if a data selection is performed against a OneWorld Left Outer Join business view for a column in the TO (secondary) table, the OneWorld Database Middleware logic appends a 'OR <column> IS NULL' clause to the SQL Query. This behavior has existed in OneWorld prior to B733 base and will be preserved since existing applications rely upon this behavior. In order to preserve the existing behavior and also support the standard SQL 92 Left Outer Join, a new option (called SQL 92 Left Outer Join) is provided through the OneWorld Business View Design Aid tool (in ERP9).