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



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.



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.