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

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


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 those
columns 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 from
the primary file; that could replace the other predicates in the WHERE
clause to make a very specific test.

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