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

plain like this works:
select MHORDR FROM
SHP4VAR27F.MFH1MH WHERE MHORDR = '0257666101'

however when adding in the fancy substringing, no can do.

1. Token . was not valid. Valid tokens: , FROM INTO.
select MHORDR, LEFT(MHORDR,8), SUBSTR(MHORDR,1,8), FROM
SHP4VAR27F.MFH1MH WHERE MHORDR = '0257666101'



2. Token . was not valid. Valid tokens: , FROM INTO.
select MHORDR, LEFT(MHORDR,8), FROM
SHP4VAR27F.MFH1MH WHERE MHORDR = '0257666101'

3. Token . was not valid. Valid tokens: , FROM INTO.
select MHORDR, SUBSTR(MHORDR,1,8), FROM
SHP4VAR27F.MFH1MH WHERE MHORDR = '0257666101'


so I am
deducing that you cannot select on a field like this.


On Thu, Oct 3, 2013 at 11:13 AM, <rob@xxxxxxxxx> wrote:

Thanks for the first part of the email, the field definitions.
What about the select given to try to show us a MHORDR that is supposed to
match the respective OHORD#?

select MHORDR, LEFT(MHORDR,8), SUBSTR(MHORDR,1,8),
FROM SHP4VAR27F.MFH1MH
WHERE MHORDR ...

no joining. Just put a value at the end of the where clause above.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Hoteltravelfundotcom <hoteltravelfun@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>,
Date: 10/03/2013 11:08 AM
Subject: Re: how to include a partial field in this view?
Sent by: midrange-l-bounces@xxxxxxxxxxxx



Field Type Length Length Position Usage
Heading
OHORD# CHAR 8 8 6 Both ORDER
NUMBER

MHORDR CHAR 10 10 6 Both ORDER
NUMBER
Field text . . . . . . . . . . . . . . . : ORDER
NUMBER

its' not making much sense why the data is not getting in..

I did a hokey way in the past where I had a simple RPG program MOVEL into
a
work file. and it was fine. But this is type of method' I have to leave
behind.




On Thu, Oct 3, 2013 at 10:58 AM, <rob@xxxxxxxxx> wrote:

Getting closer. Now:
What is the field definition for ASTDTA.OEORHDOH.OHORD#
What is the field definition for SHP4VAR27F.MFH1MHM.MHORDR
Now try picking one you think should match and do a simple
select MHORDR, LEFT(MHORDR,8), SUBSTR(MHORDR,1,8),
CAST(MHORDER AS CHAR (8))
FROM SHP4VAR27F.MFH1MH
WHERE MHORDR ...

and you'll be able to see what the results of all of those functions are
and which one matches (if any) with what you are expecting.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Hoteltravelfundotcom <hoteltravelfun@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>,
Date: 10/03/2013 10:04 AM
Subject: Re: how to include a partial field in this view?
Sent by: midrange-l-bounces@xxxxxxxxxxxx



Rob, here as well we are not getting the data

ORDER DATE ORDER JOB ORDER NUMBER CUST ZONE
TYPE NUMBER
20,120,101 INT 03500017 -
20,120,101 INT 03500018 -
20,120,101 INT CMB 03500020 -
20,120,101 INT 03500058 -
20,120,101 INT 03500079 -


On Thu, Oct 3, 2013 at 9:21 AM, <rob@xxxxxxxxx> wrote:

Can you simplify this with a test of
SELECT
T01.OHORDD, T01.OHORDT, T01.OHJOB3, T01.OHORD#,
M.MHZONE
FROM
ASTDTA.OEORHDOH T01
LEFT OUTER JOIN
SHP4VAR27F.MFH1MH M
ON T01.OHORD# = left(M.MHORDR, 8)
WHERE
T01.OHOSTC = 'CL'
AND T01.OHORDD >= 20120101


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Hoteltravelfundotcom <hoteltravelfun@xxxxxxxxx>
To: Midrange Systems Technical Discussion

<midrange-l@xxxxxxxxxxxx>,

Date: 10/03/2013 09:14 AM
Subject: Re: how to include a partial field in this view?
Sent by: midrange-l-bounces@xxxxxxxxxxxx



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.

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


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

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


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

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


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