Max Decode SQL Problem

[Jack_Maloney]

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

[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.

[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

[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.

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.