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

I have this View runs but it is not bringing in the data from the
SHP4VAR27F table, that is, the field we want , MHZONE, is blanks but there
is data there. It shows as a '-' in the i when i 'view ' the view.
CREATE VIEW OKLIB/FRTZONE AS
SELECT
T01.OHORDD, T03.IHINV#, T01.OHORDT, T01.OHJOB3, T01.OHORD#,
T02.IDPRLC, T02.IDNTU$*(IDSHP#) AS EXTSHP, T02.IDPRT#,
M.MHZONE
FROM
ASTDTA.OEORHDOH T01
LEFT OUTER JOIN
ASTDTA.OEINDLID T02
ON T01.OHORD# = T02.IDORD#
LEFT OUTER JOIN
ASTDTA.OEINHDIH T03
ON T01.OHORD# = T03.IHORD#
LEFT OUTER JOIN
SHP4VAR27F.MFH1MH M
ON T01.OHORD# = left(M.MHORDR, 8)
WHERE
T01.OHOSTC = 'CL'
AND T01.OHORDD >= 20120101








On Wed, Oct 2, 2013 at 6:40 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:

On 02 Oct 2013 14:53, Hoteltravelfundotcom wrote:

We have here 3 files which are linked basically by Order number.
There is one more file we need to use to get the Shipping zone code,
from the "Varsity" system. This field however is 10 pos. whilst the
others are all 8. We want to join on MHORDR in the file
SHP4VAR27F/MFH1MHL0, then we are done.
How am I to add this in here?

SELECT ALL
T01.OHORDD, T03.IHINV#
, T01.OHORDT, T01.OHJOB3, T01.OHORD#
, T02.IDPRLC, T02.IDNTU$*(IDSHP#) AS EXTSHP, T02.IDPRT#
FROM ASTDTA/OEORHDOH T01
LEFT OUTER JOIN ASTDTA/OEIND1 T02
ON T01.OHORD# = T02.IDORD#
LEFT OUTER JOIN ASTDTA/OEINHDIH T03
ON T01.OHORD# = T03.IHORD#
WHERE T01.OHOSTC = 'CL'
AND T01.OHORDD>= 20120101
ORDER BY T01.OHORD# ASC

Knowing just that two columns have a definitional difference of "10
pos" vs "8 pos" is an incomplete description. Also what is "this field"
is not noted... and the "include a partial field" seems to refer to what
data is returned, but that makes little sense, so I infer the intent is
to use "a partial field" in a predicate. Yet *assuming* that the last
two bytes of each value in the larger column is always blanks, then
because trailing blanks are insignificant, there is nothing special to
do to make the comparison\predicate.

If I understand the remaining description of the scenario, i.e. there
is a file SHP4VAR27F/MFH1MHL0 that has a single matching value of its
column MHORDR to the order number that defines each row of the result
set defined by the above SELECT [i.e. T01.OHORD#], then the following
modification adding a scalar-subselect to the SELECT should suffice:

SELECT ALL
T01.OHORDD, T03.IHINV#
, T01.OHORDT, T01.OHJOB3, T01.OHORD#
, T02.IDPRLC, T02.IDNTU$*(IDSHP#) AS EXTSHP, T02.IDPRT#
, ( select Shipping_zone_code_column
from SHP4VAR27F/MFH1MHL0 sss
where sss.MHORDR = T01.OHORD# )
FROM ASTDTA/OEORHDOH T01
LEFT OUTER JOIN ASTDTA/OEIND1 T02
ON T01.OHORD# = T02.IDORD#
LEFT OUTER JOIN ASTDTA/OEINHDIH T03
ON T01.OHORD# = T03.IHORD#
WHERE T01.OHOSTC = 'CL'
AND T01.OHORDD>= 20120101
ORDER BY T01.OHORD# ASC

--
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.