RE: SQL... MAX AND COUNT?

Well I think I answered it ;-)

But maybe not.  Then again, I'm not sure you answered it either Joe.

The OP said, "but I ONLY need those latest dates considered in the
count" so I think he's going to need MAX in there some where.  So I
think your second solution is out.  As to the first solution, even
though you've got MAX in there it's not being used to select the records
to count.

Unless I'm misunderstanding what the OP wants, I'm going to offer up my
solution again in case it was missed.

 SELECT COUNT(LDSUBC) INTO :WKCOUNT
 FROM BANKHEDR INNER JOIN LRDETAIL 
         ON QHSTCD = LDSTCD AND QHAREA = LDAREA AND QHBANK =    
         LDBANK AND LDDGRP = QHHGRP AND LDCATC = :WKCATCD 
 WHERE QHHGRP = :WKGROUP AND QHRSTA = 'A' AND QHMAIN = :WKMAIN    
   and LDQDAT = ( SELECT MAX(LDQDAT)
                  FROM LRDETAIL
                )

Side note to the OP: 
Are you sure that COUNT(LDSUB) is what you want?  To get a count of the
number of rows, one would normally use just COUNT(*).  COUNT(LDSUBC)
gives you the count of the number of non-null values in LDSUBC.
COUNT(DISTINCT LDSUBC) gives you the count of distinct non-null values.

I use COUNT(*) and COUNT(DISTINCT whatever) all the time, but have never
needed COUNT(whatever).  If LDSUBC doesn't ever contain null values,
then COUNT(LDSUBC) gives the same results at COUNT(*) but as noted, goes
about it in a different way.  If you want to count rows, you should use
COUNT(*).


HTH,


Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx 
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Joe Pluta
Sent: Wednesday, January 03, 2007 11:57 AM
To: 'RPG programming on the AS400 / iSeries'
Subject: RE: SQL... MAX AND COUNT?

I see nobody answered this, so let's get to it.

First, you need to join two files based on some key values.  
Then you need
to create a list of rows that basically has one record per 
key.  Finally,
you need a count of those rows.  This is what Common Table Expressions
(CTEs) are for.  Nothing in your statement says what the 
unique keys are,
but they're probably your join keys.  Assuming that all the 
keys are part of
the distinct key you'd do the following:

C/EXEC SQL
C+ WITH T1 AS
C+ (SELECT
C+    QHSTCD, QHAREA, QHBANK, LDGRP, LDCATC, MAX(LDQDAT)
C+ FROM BANKHEDR INNER JOIN LRDETAIL ON
C+    QHSTCD = LDSTCD AND QHAREA = LDAREA AND QHBANK = LDBANK 
C+    AND LDDGRP = QHHGRP AND LDCATC = :WKCATCD
C+ WHERE
C+    QHHGBRP = :WKGROUP AND QHRSTA = 'A' AND QHMAIN = :WKMAIN
C+ GROUP BY
C+    QHSTCD, QHAREA, QHBANK, LDGRP, LDCATC)
C+ SELECT COUNT(*) FROM T1 INTO :WKCOUNT
C/END-EXEC

Line 1 identifies the CTE named T1.  Lines two and three 
select the unique
key fields as well as the MAX field.  (This is what you asked 
for, although
it isn't what you want <grin>.  I'll get to that in a 
moment.)  The next
three lines define the JOIN, while the next two lines define 
the selection
criteria (WHERE).  The next two lines (GROUP BY) is what 
causes the grouping
and allows you to select one line per set of keys.  It also 
closes the CTE.
The last line simply counts the rows in the CTE.

However, note something.  Although you calculate the maximum 
date, you don't
use it.  In fact, you don't care about the date.  You simply 
want one record
per key.  It's even easier to do that:

C/EXEC SQL
C+ WITH T1 AS
C+ (SELECT DISTINCT
C+    QHSTCD, QHAREA, QHBANK, LDGRP, LDCATC
C+ FROM BANKHEDR INNER JOIN LRDETAIL ON
C+    QHSTCD = LDSTCD AND QHAREA = LDAREA AND QHBANK = LDBANK 
C+    AND LDDGRP = QHHGRP AND LDCATC = :WKCATCD
C+ WHERE
C+    QHHGBRP = :WKGROUP AND QHRSTA = 'A' AND QHMAIN = :WKMAIN)
C+ SELECT COUNT(*) FROM T1 INTO :WKCOUNT
C/END-EXEC

DISTINCT returns one row for each unique combination of 
values in the SELECT
list.  Not only that, it performs better because the enhanced 
query engine
is smart enough to use SETGT/READ logic to skip over 
unnecessary records,
whereas using an aggregate function such as MAX forces every row to be
processed.  My guess is that depending on the number of 
duplicate rows, the
second version would run much quicker.

Joe

From: Jay Vaughn

The precompiler doesn't like the "AND LDQDAT = MAX(LDQDAT)" in the
following
statement, but I ONLY need those latest dates considered in 

the count...

How
do I restructure this query to consider that?

C/EXEC SQL
C+ SELECT COUNT(LDSUBC) INTO :WKCOUNT FROM BANKHEDR INNER JOIN
C+ LRDETAIL ON QHSTCD = LDSTCD AND QHAREA = LDAREA AND QHBANK =
C+ LDBANK AND LDDGRP = QHHGRP AND LDCATC = :WKCATCD WHERE QHHGRP =
C+ :WKGROUP AND QHRSTA = 'A' AND QHMAIN = :WKMAIN
C+ AND LDQDAT = MAX(LDQDAT)
C/END-EXEC

-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) 
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.