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