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

[Scott Mildenberger <SMildenberger@xxxxxxxxxxxxxxxxxx>]

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