# Max Decode SQL Problem

• 07-21-2011, 03:43 PM
Jack_Maloney
Max Decode SQL 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:

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

Now say I have the following data:

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
• 07-21-2011, 04:30 PM
Tolls
Not that this is Java, but you have told it to use the MAX() of that DECODE statement, and the last I checked 14 was greater than -1.
• 07-21-2011, 04:33 PM
Jack_Maloney
Yes I know thats the problem. My question was does anyone know how a work around for this? Im slowly going insane trying to come up with a solution.

Like is there a way of telling the code that if it finds a minus 1 then to stop everything and jump out of the Decode - the equivalent of a break clause
• 07-21-2011, 04:52 PM
Tolls
Nope, SQL doesn't work in a procedural way like that.

You need to do that MAX part first, as a subquery, so that you get the maximum ResetSequence.
Then do the above select on that, with just the DECODE.
Code:

```SELECT ReaderID, DECODE (cause, 0,sequence+ 1, -1) AS ExpectedCount FROM     (SELECT ReaderID, MAX(cause), MAX(sequence) FROM tableName     WHERE DayKey = 145     GROUP BY ReaderID)```
Something along those lines. The inner query gives you your max sequence. The MAX(cause) should highlight the non-zeros, though you might want to decode to a boolean of some sort here instead, depending on what your real data looks like.