Re: SQL Internal Error

SQL -901 errors are, in my opinion, IBM defects. They tend to occur only
in complex SQL. I've always just tweaked the code until the error
eventually disappears. I've worked around the problem about 30 times.

Usually, the job log contains an MCH error as well.

Because they are defects, they don't tend to make sense. For me, the SQL
failing has always been valid. The common thread is complex SQL.

Good luck,

Mike

On Tue, Mar 12, 2019 at 2:43 AM Craig Richards <craig@xxxxxxxxxxxxxxxx>
wrote:

Hi All,

I'm getting an SQL System Error from one of my subprocedures and it looks
like an embedded SQL bug to me.

To be precise, the code is running and returning SqlCode -901, SqlState
58004 and producing a couple of spooled files:
DBOPTRC198, QSQSVCDMP, QDSPJOB

The text retreived from:
Get Diagnostics Condition 1 :ErrorMsg = MESSAGE_TEXT is returning "SQL
System Error".

I would appreciate it if anyone could have a quick look at the SQL and my
problem description and double check I'm not being a buffoon (in this
matter at least...)
Below are links to two versions of the SQL - one which works and one which
fails and the only difference is an extra field in the main select and
final group by.

My best understanding of what is causing the internal error is that the
GROUP BY clause only contains host variables, plus a field on the main
WHERE clause which also refers to a host variable.
When I run the code in Squirrel SQL client (which prompts for host
variables it sees in the SQL) it runs fine - so it would seem to be an
issue with the embedded SQL.

This code works fine:
https://code.midrange.com/8ed534b6cc.html

But this code fails as described above:
https://code.midrange.com/8c22090c16.html

And the only difference between the two of them is that I dropped a field
which was not required from the main SELECT and the final GROUP BY:

From the working version:
Line 7 is dropped (it builds a field called ShipComplete from t1.OhComp)
Line 57 - r.ShipComplete is removed from the GROUP BY.

So that leaves in the GROUP BY:
1. r.OhOrdr which is part of the primary Where clause, and based on a Host
Variable (hence constant for the data set)
2. r.NumLines which is a Host Variable and therefore constant for the data
set.

As I mentioned above, cutting and pasting the exact code from the program
source to run in Squirrel SQL client, ran successfully.

Thanks to any/all who might have time for a quick look at this.
best regards,
Craig
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com