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

i removed the 'CL' at the where but this didn't help.

interesting tho. i then removed the other where statement ( the date) and
now it doesn't fetch a bunch of columns.



On Thu, Oct 3, 2013 at 4:25 PM, Hoteltravelfundotcom <
hoteltravelfun@xxxxxxxxx> wrote:

cl status just means that the order is closed. I can take that out for
sure and check later in the process. good catch. I really appreciate your
help Rob.


On Thu, Oct 3, 2013 at 4:04 PM, <rob@xxxxxxxxx> wrote:

Please take this right, but I think there's something else that is going
on here that you're not showing us. For example, I showed you the test
data I created, I showed you the select statement I used.
is it possible that you really don't have a matching row in that last
table for something in the first table with that date and that 'CL'
status?


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 04:00 PM
Subject: Re: how to include a partial field in this view?
Sent by: midrange-l-bounces@xxxxxxxxxxxx



OK this compiles but not data. I am going to make a dummy file wherein i
MOVEL to a new field and see what happens.

Is this something not set on our IBM i ? We just got a spankning new one
this past week.


On Thu, Oct 3, 2013 at 3:54 PM, <Tommy.Holden@xxxxxxxxxxxxxxxxxxxxx>
wrote:

When you assign a correlation name to a file you have to use the
correlation name to reference it's variables. Since you assigned M to
MFH1MH you would have to use M.MHORDR


Thanks,
Tommy Holden



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



I thnk we are getting somewhere:

Column or global variable MHORDR not found.
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# = SUBSTR(MFH1MH.MHORDR, 1, 8)
More...
it F4=Prompt F6=Insert line F9=Retrieve F10=Copy line


On Thu, Oct 3, 2013 at 3:33 PM, <Tommy.Holden@xxxxxxxxxxxxxxxxxxxxx>
wrote:

Leave off the schema name and see what happens
ON T01.OHORD# = SUBSTR(MFH1MH.MHORDR, 1, 8)


Thanks,
Tommy Holden



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

<midrange-l@xxxxxxxxxxxx>

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



So that means the problem is this statement

ON T01.OHORD# = SUBSTR(SHP4VAR27F.MFH1MH.MHORDR, 1, 8)


On Thu, Oct 3, 2013 at 3:24 PM, Hoteltravelfundotcom <
hoteltravelfun@xxxxxxxxx> wrote:

....+....1....+....2....+....3....+....4...
good one Tommy.
so this works.
ORDER NUMBER LEFT ( MHORDR , 8 ) SUBSTR
0257666101 02576661 02576661
******** End of data ********



On Thu, Oct 3, 2013 at 3:10 PM,

<Tommy.Holden@xxxxxxxxxxxxxxxxxxxxx>wrote:

Take the last comma out it's assuming that FROM is a column name:
select MHORDR, LEFT(MHORDR,8), SUBSTR(MHORDR,1,8) FROM
SHP4VAR27F.MFH1MH WHERE MHORDR = '0257666101'


Thanks,
Tommy Holden



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

<midrange-l@xxxxxxxxxxxx>

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



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.

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


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