Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 2 of 2

Thread: SQL Max Decode Problem

  1. #1
    Junior Member
    Join Date
    Mar 2011
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default 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


  2. #2
    Grand Poobah
    Join Date
    Mar 2011
    Posts
    1,545
    My Mood
    Grumpy
    Thanks
    0
    Thanked 167 Times in 158 Posts

    Default Re: SQL Max Decode Problem

    I'm no SQL expert but I think you are applying MAX to the results not to Cause. What I mean is that for ID 3 the first row (3 0 13) the result is 14 and for second row (3 1 13) result is -1. The MAX of 14 and -1 is 14.

    My suggestion retrieve the rows and manipulate them in your Java code.
    Improving the world one idiot at a time!

Similar Threads

  1. Replies: 0
    Last Post: June 19th, 2011, 02:16 AM
  2. [SOLVED] How to decode ENCODING=QUOTED-PRINTABLE encoded data?
    By efluvio in forum Algorithms & Recursion
    Replies: 8
    Last Post: June 13th, 2011, 04:10 PM
  3. Decode Binary Packet via TCP Socket
    By maxice in forum Java Networking
    Replies: 0
    Last Post: November 8th, 2010, 03:38 PM