On 07 Oct 2013 11:44, Hoteltravelfundotcom wrote:
yes we did just upgrade to 7.1
I will try the inner join.
Of course, be aware that any unmatched rows, would not be included in
the result-set of the INNER JOIN. As I understand the issue... a
/consistent/ result with the effects seen using LEFT OUTER JOIN, would
be an empty result-set when using the INNER JOIN; and an implication
that the issue is somewhat more likely to be a usage problem, than a
defect. And if the INNER JOIN does not yield an empty-set, such that
the matching rows would since correctly be including the previously
"missing" /zone/ data [that was previously being generated as the NULL
value], then any legitimately unmatched rows will be excluded from the
result-set rather than having the generated-as matching row with NULL
values for the columns; and an implication that the issue is more likely
a defect with the use of the LEFT or SUBSTR scalar in the join predicate
of an OUTER JOIN.
Again, the effect of using the scalar subselect vs the OUTER JOIN
[beyond just seeing the effect of using the INNER JOIN vs the OUTER
JOIN] is probably worthwhile to investigate. If that circumvents the
issue, its effects apparently would be more appropriate\desirable than
using the INNER JOIN, because unmatched rows would have the NULL value
generated for the MHZONE, instead of the unmatched row being omitted
entirely from the result-set:
SELECT
T01.OHORDD, T01.OHORDT
, T01.OHJOB3, T01.OHORD#
, T01.OHOSTC
, ( select M.MHZONE
from SHP4VAR27F.MFH1MH M
where left(M.MHORDR, 8) = T01.OHORD#
/* substr(M.MHORDR, 1, 8) = T01.OHORD# -- try this also */
) as MHZONE
FROM ASTDTA.OEORHDOH T01
WHERET01.OHORD#='02576661'
at the moment I created a temp file with the data needed
via a simple RPG MOVEL so I could push the report to the user.
IMO, suggesting that the issue was dealt with momentarily by creating
"a temp file with the data needed via a simple RPG MOVEL" is akin to
"speaking in riddles". A much more explicitly and conspicuously obvious
explanation IMNSHO would have been to suggest that the problem was
/circumvented/ by creating a temporary copy of the file and data,
whereby the column attributes of the two columns of the equal-predicate
were made identical; that the implementation for copying data was CPYF
vs an RPG program using MOVEL is inconsequential, and the relevant
detail is that the compared columns are since made compatible.
To make the effect extremely explicit, a scripted example is IMO
significantly clearer than just /words/ attempting to describe what was
done; e.g.:
Given [effectively]:
create table OEORHDOH (OHORD# char(08)) ;
create table MFH1MH (MHORDR char(10), MHZONE char(2)) ;
insert into OEORHDOH values('02576661') ;
insert into MFH1MH values('0257666101', 'NW') ;
The problem being encountered; unexpected NULL values in JOIN:
SELECT T01.OHORD#, M.MHZONE
FROM OEORHDOH T01
LEFT OUTER JOIN MFH1MH M
ON T01.OHORD# = left(M.MHORDR, 8)
/* T01.OHORD# = substr(M.MHORDR, 1, 8) -- same problem */
WHERE T01.OHORD#='02576661'
; -- yields the following report:
OHORD# MHZONE
02576661 -
******** End of data ********
Having made the following [effective] changes:
alter table MFH1MH alter column MHORDR set data type char(8) ;
The problem was circumvented [but intuitively, there should be no
requirement to effect matching column attributes]:
SELECT T01.OHORD#, M.MHZONE
FROM OEORHDOH T01
LEFT OUTER JOIN MFH1MH M
ON T01.OHORD# = M.MHORDR /* no SUBSTR nor LEFT scalar */
WHERE T01.OHORD#='02576661'
; -- yields the following report:
OHORD# MHZONE
02576661 NW
******** End of data ********
Irrespective of the means to circumvent the difficulty [an apparent
defect], such actions are not a resolution; merely a /circumvention/ of
the problem. The following predicates should be equivalent in effect,
to the effect from having copied\moved the data for field MHORDR into a
CHAR(8) column of an alternate copy of the file.
T01.OHORD# = left(M.MHORDR, 8)
T01.OHORD# = substr(M.MHORDR, 1, 8)
With very few if any possible nuances [e.g. per some details not yet
revealed in the message thread], there is effectively _no reason_ other
than a defect in\with the DB2 for i SQL, for which the NULL values
should be generated in the described scenario [at least for the order
number '02576661']; i.e. the implication was made clearly enough, that
there *is a matching row* for that order-number. Thus it would behoove
the sufferer of such an issue, to report that failure as a defect, to
their service provider, to get a correction; a preventive fix. If the
latest cumulative and DB-fixpack are not already [properly] installed,
then that will be the first thing almost any support organization will
want to have happen before they review the incident.... unless a
scripted re-create is provide to them, that reproduces the same problem
on their up-to-date system.
midrange.com
