Re: SQL Indicator?

Thanks everyone. My program running fine now, with me having implemented your suggestions.

Some of this I simply overlooked ... I been swamped with end-year, where each year the rules change on what is wanted, and the volume increases. This year there is the added complication that to save money on telecommunications etc. the company is tolerating much more downage and non-working connections.
:

Al,

A couple of points:

Can you temporarily create a view (or LF) over ITH using the columns tprod,
ttype, ttdte, and twhs? It can significantly reduce the search time. Also,
if you know the file's data distribution, you can arrange the key fields to
reduce as much data as quickly as possible. For example, if you have a 10
million row table, and tprod on one prod type is 100,000 records, the
successive filter conditions will execute faster. Sometimes, creating the
index key fields in the same order as the conditions in the where clause
also supplies a performance boost; however the query optimizers are good at
rewriting queries to perform optimally.

To make the typing a little easier and perhaps more readable, you can also
use the IN and BETWEEN keywords. IN specifies a list of values, and BETWEEN
allows for range processing (>= and <=). I would write your query like this:

exec sql
select coalesce(sum(qty),0) into :issues
from bakbp813/ith
where tprod=:iprod
and ttype in ('CI','I')
and ttdte between :datlo and :dathi
and twhs between '21' and '69';

HTH,
Loyd


> Here is the code which has been running fine for years:
> >
> > C*EXEC SQL
> > C* SELECT SUM(TQTY) INTO :ISSUES
> > C* FROM ITH WHERE TPROD=:IPROD
> > C* AND (TTYPE='CI' OR TTYPE='I')
> > C* AND TTDTE>=:DATLO
> > C* AND TTDTE<=:DATHI
> > C* AND TWHS>='21'
> > C* AND TWHS<='59'
> > C*END-EXEC
> >
> > Here is what I added (recent archives of the data) that apparently blew
> > up
> >
> > C*EXEC SQL
> > C* SELECT SUM(TQTY) INTO :ISSUES
> > C* FROM BAKBP813/ITH WHERE TPROD=:IPROD
> > C* AND (TTYPE='CI' OR TTYPE='I')
> > C* AND TTDTE>=:DATLO
> > C* AND TTDTE<=:DATHI
> > C* AND TWHS>='21'
> > C* AND TWHS<='69'
> > C*END-EXEC
> >
> > There will be cases where there is no TQTY activity where it is looking
> > to get a total of inventory consumed in the specified date range.
> >
> > Al Macintyre
>
>
--
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.