Problem with TOraQuery, DataSetProvider and Order by DECODE

[zedmartins]

I have a OraQuery with this SQL:

SELECT
MR.COLUMN1,
MR.COLUMN2,
MR.COLUMN3
FROM
MULTI_ROW_TABLE MR,
SINGLE_ROW_TABLE SR
ORDER BY
DECODE(SR.COLUMN,3,MR.COLUMN3,2,MR.COLUMN2,MR.COLUMN1)

The Single Row table contains a column that defines how the report will be ordered. If I take this query and connect it to a DataSetProvider to generate data to a ClientDataSet, the data is printed using a wrong order.

Debugging the ODAC source, i found the problem on DBAccess.pas. The TCustomDADataSet.PSGetDefaultOrder method uses a Parser to extract the indexed fields from the ORDER BY item of the SQL statement. But it doesn't work right when the ORDER BY has a function in it.

I had modified the DBAccess.pas to ignore the Parser thing when the ORDER BY has a function, and it worked fine, at least it worked for me.

Just wanted you guys to know this!

thanks!

[Plash]

Please specify what column names do you return from the PSGetDefaultOrder method. If we return an expression with the DECODE function, an error occurs.

[zedmartins]

what I am doing when the ORDER BY has the DECODE function is return an empty string as if there was now ORDER BY clause. But I don't know if this is the best solution.

Regards,

[Plash]

We'll consider possibility to improve the PSGetDefaultOrder method, but it is hardly to happen in the nearest future.