Re: Different result from full outer join in ACS RunSqlScript and SQLRPGLE -- RPG400-L

What you think you're seeing, a difference running the same SQL
statement in ACS vs embedded in RPG, is not what's actually happening.

It's simple really, both ACS and RPG simply pass a statement to the DB.
The DB actually does the work.

Now the differences in the run-time defaults, may mean a given query gets
processed differently; ie. indexes used, the time it takes to return. But
the results will be exactly the same.

So what does this mean? It means that the query isn't actually the same.

From personal experience, there's likely a copy/paste induced difference.

I've even seen non-printable characters ignored by ACS cause an issue with
embedding the statement in RPG.

Now having said that, I'll say the above is true 99.99999% of the time.

The other 0.00001%, a bug in the OS causes the difference in run-time
environments to surface different results.

Note this isn't RPG vs ACS, it's just that the defaults settings *FIRSTIO
vs. *ALLIO, memory available to the QZDASOINIT job vs. interactive...ect.
If you configure the environments the same, you'd get the same right/wrong
answer from each tool.

If you run a DB monitor and capture each invocation, you might be able to
tell where the statement is different.

Or you've got something to hand to IBM and ask them what is going on.

HTH,
Charles

On Thu, Nov 2, 2023 at 8:07 AM Martijn van Breden <
m.vanbreden@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Hi midrange experts 🙂

I have a inexplicable difference between the outcome of a query when run
in SQLRPGLE and ACS-RSS.

It's a query that determines differences between two tables in two
different libraries. The table definitions are identical. To get to the
tables in the right libraries I create two aliases in qtemp first.

I select a couple of columns left and right and do a full outer join on
three columns to get what's only in the left table, in both tables and only
in the right table. From that I determine the differences.

I developed this using ACS -RSS and experimented until it worked fine.
Then I built it into a function in an SQLRPGLE-program. It worked quite
nice except for one thing.
That is that it did not see differences on the column that I use for the
full outer join. When I use exactly the same query (execpt for the
into-clause) in ACS it does see those differences.

After adding some more columns (for counting options left and right) and
a lot of debugging I found out that the full outer join in RPG is not
returning the rows that are in only one of the tables, where in ACS, it
does return them. I then dove into the SQL "Set option" statement assuming
that there was some kind of null-value-behaviour-setting that was messing
up my results. However, I can't find anything that explains what I'm
experiencing

At the bottom you see the query:
The DMOF table has MENAM as a header key (from another table) and TASKB,
MTASK as primary key. Differences between other columns (like OTFMT and
TSKLV) are determined per MTASK row if they exist on the left and right
table. At the end I'm rounding up to see if there are differences per MENAM
group.

If Left has the values
MENAM
X
MTASK
A
B

And Right has
MENAM
X
MTASK
A
B
C
D

Then in ACS-RSS I see LftOpt = 2 and RgtOpt = 4 and MTASK_Dif = '1' which
is correct
In SQLRPGLE I see LftOpt = 2 and RgtOpt = 2 and MTASK_Dif = '0' which is
wrong

Does anyone have an idea what is causing this different behaviour? Is RPG
influencing the execution of the query?

Just to clutter your mail boxes :-)
-- leftData from DMOF_Left alias and RightData from DMOF via DMOF_Right in
another library
with leftData as (
select '1' as Found_Lft,
TASKB as TASKB_Lft,
MENAM as MENAM_Lft,
MTASK as MTASK_Lft,
MAWNR as MAWNR_Lft,
OTFMT as OTFMT_Lft,
OPTYP as OPTYP_Lft,
TSKLV as TSKLV_Lft,
OTTGT as OTTGT_Lft,
OPACT as OPACT_Lft,
MOKTX as MOKTX_Lft,
MSHTP as MSHTP_Lft
from Dmof_left
),
RightData as (
select '1' as Found_Rgt,
TASKB as TASKB_Rgt,
MENAM as MENAM_Rgt,
MTASK as MTASK_Rgt,
MAWNR as MAWNR_Rgt,
OTFMT as OTFMT_Rgt,
OPTYP as OPTYP_Rgt,
TSKLV as TSKLV_Rgt,
OTTGT as OTTGT_Rgt,
OPACT as OPACT_Rgt,
MOKTX as MOKTX_Rgt,
MSHTP as MSHTP_Rgt
from Dmof_Right
),
-- These are the join columns MENAM. TASKB and MTASK
AnzMods as (
select coalesce(MENAM_Lft, MENAM_Rgt) as MENAM_All,
coalesce(TASKB_Lft, TASKB_Rgt) as TASKB_All,
coalesce(MTASK_Lft, MTASK_Rgt) as MTASK_All,
-- for counting options
case
when Found_Lft is null then 0
else 1
end as LftCount,
case
when Found_Rgt is null then 0
else 1
end as RgtCount,
-- for getting a proper sequence (not used here)
case
when MAWNR_Lft is null then '99' || MAWNR_Rgt
when MAWNR_Rgt is null then MAWNR_Lft || '00'
else MAWNR_Lft || MAWNR_Rgt
end as MAWNR_Seq,
-- if MTASK is only present in one table I give it a '1' otherwise a '0'
case
when ifnull(Found_Lft, '0') != ifnull(Found_Rgt, '0') then
'1'
else '0'
end as MTASK_Mod,
-- if present on both sides detemine differences for other columns
case
when ifnull(Found_Lft, '0') = '1'
and ifnull(Found_Rgt, '0') = '1'
and ifnull(MAWNR_Lft, '') != ifnull(MAWNR_Rgt, '') then
'1'
else '0'
end as MAWNR_Mod,
case
when ifnull(Found_Lft, '0') = '1'
and ifnull(Found_Rgt, '0') = '1'
and ifnull(OTFMT_Lft, '') != ifnull(OTFMT_Rgt, '') then
'1'
else '0'
end as OTFMT_Mod,
case
when ifnull(Found_Lft, '0') = '1'
and ifnull(Found_Rgt, '0') = '1'
and ifnull(OPTYP_Lft, '') != ifnull(OPTYP_Rgt, '') then
'1'
else '0'
end as OPTYP_Mod,
case
when ifnull(Found_Lft, '0') = '1'
and ifnull(Found_Rgt, '0') = '1'
and ifnull(TSKLV_Lft, '') != ifnull(TSKLV_Rgt, '') then
'1'
else '0'
end as TSKLV_Mod,
case
when ifnull(Found_Lft, '0') = '1'
and ifnull(Found_Rgt, '0') = '1'
and ifnull(OTTGT_Lft, '') != ifnull(OTTGT_Rgt, '') then
'1'
else '0'
end as OTTGT_Mod,
case
when ifnull(Found_Lft, '0') = '1'
and ifnull(Found_Rgt, '0') = '1'
and ifnull(OPACT_Lft, '') != ifnull(OPACT_Rgt, '') then
'1'
else '0'
end as OPACT_Mod,
case
when ifnull(Found_Lft, '0') = '1'
and ifnull(Found_Rgt, '0') = '1'
and ifnull(MOKTX_Lft, '') != ifnull(MOKTX_Rgt, '') then
'1'
else '0'
end as MOKTX_Mod,
case
when ifnull(Found_Lft, '0') = '1'
and ifnull(Found_Rgt, '0') = '1'
and ifnull(MSHTP_Lft, '') != ifnull(MSHTP_Rgt, '') then
'1'
else '0'
end as MSHTP_Mod
from LeftData LD
-- This appears to be the source of the differences, in RPG the left-only
and right-only rows don't show up
full outer join RightData RD
on MENAM_Lft = MENAM_Rgt
and TASKB_Lft = TASKB_Rgt
and MTASK_Lft = MTASK_Rgt
)
-- wrap up on group level -> if at least one difference is found in a line
of the group, the group is different
select
Sum(LftCount) as LftOpt,
Sum(RgtCount) as RgtOpt,
max(MTASK_Mod) as MTASK_Dif,
max(MAWNR_Mod) as MAWNR_Dif,
max(OTFMT_Mod) as OTFMT_Dif,
max(OPTYP_Mod) as OPTYP_Dif,
max(TSKLV_Mod) as TSKLV_Dif,
max(OTTGT_Mod) as OTTGT_Dif,
max(OPACT_Mod) as OPACT_Dif,
max(MOKTX_Mod) as MOKTX_Dif,
max(MSHTP_Mod) as MSHTP_Dif,
'0' as found, '0' as Error
from AnzMods where Menam_All = :MENAM_INM
group by menam_all;

Thanks in advance,
Kind regards, Met vriendelijke groet,

Martijn van Breden
lead software architect

E-mail disclaimer<https://www.pantheon-automatisering.nl/disclaimer/>

--
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@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.