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.