Thread: SQL Max Decode Problem

Hi,

I have a problem within a Java application using a MAX DECODE SQL statement. So heres a sample of the code im using:

SELECT ReaderID,
MAX(DECODE (cause, 0,sequence+ 1, -1)) AS ExpectedCount, ---Problem Line
FROM tableName
WHERE DayKey = 145
GROUP BY ReaderID

Now say I have the following data:

ReaderID, Cause, ResetSequence
1 0 13
1 0 13
2 1 13
2 1 13
3 0 13
3 1 13

For ReaderID 1 it will set the ExpectedCount to 14 ... Correct
For ReaderID 2 it will set the ExpectedCount to -1 ... Correct
For ReaderID 3 it will set the ExpectedCount to 14 .. Incorrect. If any of the causes for a particular ReaderID is set to 1 then the Reset sequence should be set to -1!

So the problem is that its searching for the Maximum ResetSequence value for a given ReaderID - this of course will never be -1.

Can someone tell me how they would get around this problem?

Thanks,
Jack