order by decode

[luciffer]

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.

[DaPi]

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.

[luciffer]

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.

[DaPi]

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

[luciffer]

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?

[slimdave]

Since you only have three possible orders to choose, it wouldn't be too much of a strain to do ...

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

[DaPi]

My reading of the problem implied that the SELECT was part of a CURSOR. Is that right? In which case IF won't work.

[luciffer]

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.