Re: how to include a partial field in this view?

On 04 Oct 2013 07:08, Hoteltravelfundotcom wrote:

SELECT
T01.OHORDD, T01.OHORDT
, T01.OHJOB3, T01.OHORD#
, T01.OHOSTC, M.MHZONE
FROM ASTDTA.OEORHDOH T01
LEFT OUTER JOIN
SHP4VAR27F.MFH1MH M
ON T01.OHORD# = left(M.MHORDR, 8)
WHERET01.OHORD#='02576661'

<<SNIP>> it does run and returns all data except our elusive zone.

FWiW: There was a defect a few releases and several years back ['07
to '08 timeframe] wherein the use of the SUBSTR scalar [or presumably,
an effectively equivalent] in a join predicate [as-implemented; i.e.
irrespective as-coded] produced incorrect output. However in this
message thread, there was mention of v7r1 in use.? Even if not using
IBM i 7.1, hopefully not using any system that is running with any
cumulative level as old or older than C90##vrm.?

Regardless of release\cumulative... It is entirely possible that
there is a defect being encountered, in fact seemingly probable, per the
evidence already given that suggests a matching row does exist in the
MFH1MH file:
http://archive.midrange.com/midrange-l/201310/msg00126.html

Yet given such nebulous descriptions as "returns all data except...",
an apparent lack of ability to give an explicit explanation as to what
specific data and why, with regard to the expected output, gives me
little confidence, probability-wise, whether the issue might be defect
vs usage.

FWiW: An INNER JOIN could be attempted instead, to reveal if there
might be a problem specific to the OUTER JOIN implementation. While the
scalar subselect is likely implemented the same as the OUTER JOIN,
again, a difference in the effect from using that subquery could reveal
a problem specific to the use of the OUTER JOIN syntax.

--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.