|
Run something like this with an order you just "know" should be in both
files:
SELECT
T01.OHORDD, T01.OHORDT, T01.OHJOB3, T01.OHORD#,
T01.OHOSTC,
M.MHZONE
FROM
ASTDTA.OEORHDOH T01
LEFT OUTER JOIN
SHP4VAR27F.MFH1MH M
ON T01.OHORD# = left(M.MHORDR, 8)
WHERE
T01.OHORD#='02576661'
replacing '02576661' with the known order.
Does this work?
If this does work then is it possible your other WHERE stuff is omitting
such an order?
For example, if you get a null value for M.MHZONE, then can you do a
select
*
from
SHP4VAR27F.MFH1MH M
where
M.MHORDR like '02576661%'
substituting '02576661' but leaving the percent sign, for the OHORD#
returned.
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/04/2013 09:29 AM
Subject: Re: how to include a partial field in this view?
Sent by: midrange-l-bounces@xxxxxxxxxxxx
I did the test,. which code should I run?
On Fri, Oct 4, 2013 at 9:06 AM, <rob@xxxxxxxxx> wrote:
I'm still waiting for the results of the simple test earlier...those
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 06:40 PM
Subject: Re: how to include a partial field in this view?
Sent by: midrange-l-bounces@xxxxxxxxxxxx
OK that is good. In the interim, I made a temp file by copy the records
but
with a MOVEL for the order number. the resulting
query is perfect. Why does the substring action cause so much grief?
CREATE VIEW oklib.FRTZONE AS SELECT
ALL T01.OHORDD, T03.IHINV#, T01.OHORDT, T01.OHJOB3,
T01.OHORD#,
T02.IDPRLC, T02.IDNTU$*(IDSHP#) AS EXTSHP, T02.IDPRT#,
T04.MHSTAT,
T04.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
OKLIB.MFHHMHPK T04
ON T01.OHORD# = T04.MHORDP
WHERE T01.OHOSTC = 'CL'
AND T01.OHORDD >= 20120101
On Thu, Oct 3, 2013 at 4:52 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:
On 03 Oct 2013 13:32, Hoteltravelfundotcom wrote:
I removed the <ed: predicate T01.OHOSTC = > 'CL' in the WHERE but
this didn't help.
Interesting tho. I then removed the other <ed: predicate in the
WHERE-clause; T01.OHORDD >= 20120101> ( the date) and now it doesn't
fetch a bunch of columns.
Apologies for the last empty message... some unknown sequence of
accidental keystrokes caused my NewsReader client to send the message
accidentally.
The LEFT OUTER JOIN will generate the NULL value for the columns of
unmatched rows. That is its function; i.e. the /fetched/ data for
fromcolumns was the NULL value that was generated.
Rob gave an example of a simplified query, in a prior message, that
uses a predicate specific to selecting the order-number of interest
listthe primary file; that could replace the other predicates in the WHERElist
clause to make a very specific test.
--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
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
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 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.
midrange.com
