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

Just guessing, but left returns a VARCHAR in your case which might cause the compare to fail. Try replacing with SUBSTR(M.MHORDR, 1, 8) which will return a CHAR.

OR, use Chuck's suggestion below that I left included :)

Scott Mildenberger
Programmer/Analyst
Davis Transport Inc.
Missoula, MT
406-728-5510 x128


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Hoteltravelfundotcom
Sent: Thursday, October 03, 2013 7:14 AM
To: Midrange Systems Technical Discussion
Subject: 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:

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