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-EXECLine 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-EXECDISTINCT 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. JoeFrom: Jay Vaughn The precompiler doesn't like the "AND LDQDAT = MAX(LDQDAT)" in the following statement, but I ONLY need those latest dates considered inthe 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.