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

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.