Subject: RE: SQL... MAX AND COUNT? From: "Wilt, Charles" Date: Wed, 3 Jan 2007 12:58:40 -0500 List-archive: List-help: List-id: RPG programming on the AS400 / iSeries List-post: List-subscribe: , List-unsubscribe: ,

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

```
```
```