Re: SQL Indicator?

[Loyd Goodbar <loyd@xxxxxxxxxxxxxx>]

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