|
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
