Results 1 to 4 of 4
  1. #1
    Jack_Maloney is offline Member
    Join Date
    Mar 2011
    Posts
    6
    Rep Power
    0

    Default 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:

    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

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,755
    Rep Power
    19

    Default

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

    Default

    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. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,755
    Rep Power
    19

    Default

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

Similar Threads

  1. decode binary to characters
    By greg677 in forum New To Java
    Replies: 11
    Last Post: 04-17-2010, 03:48 PM
  2. Decode this piece of Code
    By mikeyl62 in forum New To Java
    Replies: 2
    Last Post: 02-27-2010, 08:59 PM
  3. code and decode?
    By jeffrey in forum New To Java
    Replies: 5
    Last Post: 08-07-2009, 09:18 AM
  4. Getting problem in UTF-8 Encode/Decode with Java
    By sagarsway in forum Advanced Java
    Replies: 2
    Last Post: 12-22-2008, 07:01 PM
  5. MimeUtility.decode encoding
    By mwildam in forum Advanced Java
    Replies: 2
    Last Post: 08-19-2008, 02:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •