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

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