order by decode
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: order by decode

  1. #1
    Join Date
    Dec 2003
    Posts
    90

    order by decode

    I want to order a query by a param passed to the sp, the table is this.

    ID Number(12)
    NAMEE Varchar2(256)
    NAMEF Varchar2(256)
    POINTERE Varchar2(256)
    POINTERF Varchar2(256)
    DATECREATED DATE
    DATEMODIFIED DATE
    DOWNLOADCOUNT NUMBER(9)
    SUMMARYE Varchar2(400)
    SUMMARYF Varchar2(400)

    The query
    SELECT id,
    DECODE(UPPER(AUserLangPref), 'F', caw_dataobjects.NAMEF, 'E', caw_dataobjects.NAMEE) NAME,
    DECODE(UPPER(AUserLangPref), 'F', caw_dataobjects.POINTERF, 'E', caw_dataobjects.POINTERE) POINTER,
    DECODE(UPPER(AUserLangPref), 'F', caw_dataobjects.SUMMARYF, 'E', caw_dataobjects.SUMMARYE) SUMMARY,
    caw_dataobjects.DATECREATED,
    caw_dataobjects.DATEMODIFIED,
    caw_dataobjects.DOWNLOADCOUNT
    FROM caw_dataobjects
    WHERE id IN ( SELECT *
    FROM TABLE(CAST(DataObjectListPaged as OneDNumberArray))
    )
    ORDER BY DECODE(ASortOption, 0, caw_dataobjects.DATEMODIFIED,
    1, caw_dataobjects.DATECREATED,
    2, caw_dataobjects.DOWNLOADCOUNT,
    caw_dataobjects.DATEMODIFIED) DESC

    I get a compile error that says:

    PL/SQL: ORA-00932: inconsistent datatypes: expected DATE got NUMBER

    on line "2, caw_dataobjects.DOWNLOADCOUNT,". If i change this to any other field in the table (namee, summarye) whatever it compiles ad runs fine.

    Any ideas on what the problem is? Also if someone knows a way i can specify the sort order with the column in the one decode that would be nice.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    The error message says it all! The DECODE must always output the same datatype, not a mixture of number & date (I presume that when you mix date & varchar, there is an implicit conversion to varchar - I'm sure Jurij will confirm ).

    There are several ways to kludge DOWNLOADCOUNT to a date - but they will fail if you really use all 9 digits.

    I'm assuming ASortOption is a constant for the query - then something like this?

    ORDER BY
    DECODE(ASortOption,
    0, caw_dataobjects.DATEMODIFIED,
    1, caw_dataobjects.DATECREATED,
    2, to_date('01-JAN-1700','DD-MON-YYYY'),
    caw_dataobjects.DATEMODIFIED) DESC,
    DECODE(ASortOption,
    2, caw_dataobjects.DOWNLOADCOUNT,
    0) DESC

    There is probably a more elegant solution using dynamic SQL.

  3. #3
    Join Date
    Dec 2003
    Posts
    90
    ASortOption is the param passed to the SP for sorting.

    I figured from the error that you couldn't mix types in the decode of an order by, but wanted confirmation...thanks.

    Would there be another way all together besides decode?

    I agree that your decode on an early date (ie: 01-01-1700) and then another decode would work, but if there is another method i'd like to see it.

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    No, it's not an early date, it's a CONSTANT date. There is no assumption that it is less than any data in the db (otherwise I've misunderstood your use of ASortOption). (It was habit - I ran an old system that would not allow NULL's for unknown birthdates - we used Jan,01 1700 for that!)

    The elegant way to do it, is to build the query as a varchar string according to the value of ASortOption and execute as dynamic SQL, perhaps like:
    http://www.csee.umbc.edu/help/oracle...9dyn.htm#23821

  5. #5
    Join Date
    Dec 2003
    Posts
    90
    What would be the performance impact of doing the elegant dynamic query over using the decode with the constant date?

    how does a dynamic query affect the execution plan?

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Since you only have three possible orders to choose, it wouldn't be too much of a strain to do ...
    Code:
    ...
    if p_Sort_Option = 1 Then
    -- query with order by DATECREATED
    Elsif p_Sort_Option = 2 Then
    -- query with order by downloadcount
    Else
    -- query with order by DATEMODIFIED
    End If;
    ...
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    My reading of the problem implied that the SELECT was part of a CURSOR. Is that right? In which case IF won't work.

  8. #8
    Join Date
    Dec 2003
    Posts
    90
    i am returning a cursor from the SP, i open this cursor for the query.

    The query is approx 90 lines long, so doing an if and repeating the query several times would get very messy. As well there could be the chance of adding in ore sort options at a later time therefore it is not a good assumption that there is only 3 sort options. This is why I am trying to stay away from the if and switch ways of doing it.

Posting Permissions

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



Click Here to Expand Forum to Full Width