# Thread: Max Decode SQL Problem

1. Member
Join Date
Mar 2011
Posts
6
Rep Power
0

## 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

2. Moderator
Join Date
Apr 2009
Posts
13,541
Rep Power
28
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.

3. Member
Join Date
Mar 2011
Posts
6
Rep Power
0
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

4. Moderator
Join Date
Apr 2009
Posts
13,541
Rep Power
28
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.
Java Code:
```SELECT ReaderID,
DECODE (cause, 0,sequence+ 1, -1) AS ExpectedCount
FROM
(SELECT ReaderID, MAX(cause), MAX(sequence) FROM tableName
WHERE DayKey = 145