Hello Martijn
Have you looked at the COMPARE_FILE table function? This was delivered in 7.4 of the OS. It will report where 2 tables differ - it can even be used to compare tables on different RDB entries, hence, different LPARs, I believe.
Cheers
Vern
On Thu, 2 Nov, 2023 at 9:10 AM, Martijn van Breden <m.vanbreden@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
To: rpg400-l@xxxxxxxxxxxxxxxxxx
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/><
https://www.pantheon-automatisering.nl/disclaimer/>>
midrange.com
