Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    6

    Question Unanswered: "Order by xxx Desc" does not work with DB 8.1 FP2.

    "Order by xxx Desc" does not work with DB 8.1 FP2.

    We use Type 4 Driver (DB2Java.zip), to connect our java application running on IBM JRE 1.3.1 to DB2 8.1 FP2 database.

    A select query is executed repeatedly to fetch results in batches. For example, if the Select query returns 100 results, we store only the first 10 from the fetched results in a two dimensional array and then return the array. When the user clicks "next" same query is executed again and next only 10 results are returned.

    This is basically done for page-wise display of results in JSP page.

    1.Populated with same data in two different databases. One DB2 7.2 and another is DB 8.1
    2.Below query and sample results works properly in DB 7.2 database and in case of DB 8.1 FP 2 same results are returned again and again.
    3. Application and databases run on AIX version 5.2 machines.
    4. Keyword DISTINCT in SQL query is not required for our requirement. We get unique results always without distinct also.
    5. Problem happens obly for "Order by DESC". " Order by Asc" works fine.

    IS THIS IS A BUG WITH DB2JAVA.ZIP DRIVER FILE

    Table Structure
    |
    Table Name : | Table Name :
    MESSAGE | STATUS
    |
    Columns: | Columns:
    MessageID | StatusID
    Subject | StatusName
    StatusID (Req.)|

    SQL Query

    SELECT MessageID, Subject,
    COALESCE(UPPER(case when m.StatusID < 9 then 'Active' when m.StatusID = 9 then 'Complete' else 'Unknown State' end ),' ') STATUS
    FROM MESSAGE m, STATUS s
    WHERE m.StatusID = s.StatusID
    ORDER BY STATUS DESC


    MessageID Subject STATUS
    2 xxxx Complete
    6 yyyy Complete
    1 bbbb Complete
    3 cccc Complete
    5 aaaa Active
    7 cdfs Active
    9 rhtf Active
    4 khjk Active
    6 thjh Active
    11 sdfg Active
    <-- First round
    13 ghgh Active
    32 jkhj Active
    34 iuyy Active
    14 uiui Active
    15 rfff Active
    17 ffff Active
    19 wewe Active
    20 sdsd Active
    22 shhh Active
    21 llll Active
    <-- Second Round ( If DB 7.2 )
    13 ghgh Active
    32 jkhj Active
    34 iuyy Active
    14 uiui Active
    5 aaaa Active
    <-- Wrong Results
    7 cdfs Active
    9 rhtf Active
    4 khjk Active
    6 thjh Active
    11 sdfg Active
    <-- Second Round ( If DB 8,1 )

  2. #2
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    96

    Re: "Order by xxx Desc" does not work with DB 8.1 FP2.

    Originally posted by vinbie
    "Order by xxx Desc" does not work with DB 8.1 FP2.

    We use Type 4 Driver (DB2Java.zip), to connect our java application running on IBM JRE 1.3.1 to DB2 8.1 FP2 database.
    SQL Query

    SELECT MessageID, Subject,
    COALESCE(UPPER(case when m.StatusID < 9 then 'Active' when m.StatusID = 9 then 'Complete' else 'Unknown State' end ),' ') STATUS
    FROM MESSAGE m, STATUS s
    WHERE m.StatusID = s.StatusID
    ORDER BY STATUS DESC

    I hope I do understand your problem right but I don't see a problem

    You're sorting on the STATUS column so if it contains the same value for different rows (Active) the result will be unpredictable. If 7.2 returns them in a predictable way you are lucky, it's not because of the query.

    If you want it to be sorted on the statusid you need to include the column in your query and sort on it.

    Hope this helps.

    regards, Gert

  3. #3
    Join Date
    Jul 2003
    Posts
    6
    Thanks Gert,

    But problem occut\rs for other SQL queries alo.

    The SQL query given is a same query.

    The same problem occurs for other similar queries where we "order by DESC" on column names.

    The same queries work fine when they are ordered by same column name in Ascedning order.

Posting Permissions

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