× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.