× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. October 2013

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

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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.