RE: SQL Internal Error

Let me start by saying I don't disagree with other who suggest this is an IBM issue.

My thinking is if "A" works but "B" doesn't. Start with "A" and make small steps towards "B", watching for when it breaks.




-----Original Message-----
From: Craig Richards [mailto:craig@xxxxxxxxxxxxxxxx]
Sent: Wednesday, March 13, 2019 8:30 AM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL Internal Error

Hi Justin,
Well, it was more the other way around to be honest.

The first message I built was creating new orders and so had several fields at order-header level as well as detail lines.
The second message was to add lines to an existing order and so this message only required a couple of header-level fields.

For the second message I took away all of the extra header-level detail I didn't need - removing it from the top CTE select and final GROUP BY.
At this point it failed with the -901 error.

So I went back to the original form and went through a series of tests as I gradually removed the redundant fields.
I got it down to the last field which was the OhComp one and that worked.
When I took that away, it failed again.

I could do a test as you describe above but I'm wondering what your thinking is?
I'd be pretty sure that having OhComp directly rather than a calculated value based upon it would work fine too

Did you have a theory Justin?

On Wed, 13 Mar 2019 at 12:56, Justin Taylor <JUSTIN@xxxxxxxxxxxxx> wrote:

So you added a CASE clause that adds a new column based on t1.OhComp,
and added that new column to the ORDER BY. What happens if you add
t1.OhComp directly?



-----Original Message-----
From: Craig Richards [mailto:craig@xxxxxxxxxxxxxxxx]
Sent: Tuesday, March 12, 2019 4:43 AM
To: RPG programming on the IBM i (AS/400 and iSeries) <
rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: SQL Internal Error

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