I am trying to select the row with the largest enterdat for each entityid. See the sample data below. I only want one row from each entityid. It doesn't seem to like the 'cast'. If anyone can think of a totally different query that would be great too!

SQLCODE = -171, ERROR: THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT 1 OF + IS INVALID



SELECT *
FROM TB914 A
where (A.GIFTCLUB BETWEEN 'l2' AND 'l9'
OR A.GIFTCLUB BETWEEN 'G6' AND 'G9')
AND A.ENTITYID + Cast(VARCHAR(10),A.ENTERDAT)
in (SELECT X.ENTITYID + Cast(VARCHAR(10),MAX(X.ENTERDAT))
FROM TB914 X
WHERE X.GIFTCLUB between 'l2' and 'l9'
or (X.GIFTCLUB between 'g6' and 'g9')
GROUP BY X.ENTITYID)
ORDER BY A.ENTITYID


Sample Data:

entityid giftclub enterdat
0000000023 L3 2003-08-29 00:00:00.000
0000000023 L6 2006-04-08 00:00:00.000
0000000023 L8 2008-04-08 00:00:00.000
0000000045 G6 2006-01-18 00:00:00.000
0000000045 G7 2007-02-10 00:00:00.000

Thank you so much!!

I think you should be able to do max on date and group it by id. Here is an example:

db2 "select * from test"

C1 C2 C3
----------- ----------- ----------
11 1 12/12/2008
11 1 12/13/2008
12 2 12/22/2008
12 2 12/23/2008


db2 "select c1, max(c3) from test group by c1"

C1 2
----------- ----------
11 12/13/2008
12 12/23/2008

Thank you, db2girl.
But I would like to include all 3 columns in my result. Max(c3) wouldn't work if I included C2 in my select statement.

can you group by c1 and c2?

db2 "select c1, c2, max(c3) from test group by c1,c2"

C1 C2 3
----------- ----------- ----------
11 1 12/13/2008
12 2 12/23/2008

I am not sure what this is supposed to do:
I have never seen such a "Cast" syntax. If it means that A.ENTERDAT should be cast to VARCHAR(10), the syntax should be:
Aside from that, you try to add a string to something else. I guess you may want to use the CONCAT or '||' operators because those are the standard SQL constructs for string concatenation.

try these functions.
day(c3) month(c3) year(c3) dayofyear(c3) etc.