SQL & PL/SQL

Hi,

I've been searching around the easiest way to perform a dynamic "ORDER BY" clause and the "DECODE()" clause solution seem to be exactly what I am looking for. Unfortunately, It seems the DECODE function is not returning a correct column name value (I think it is returning NULL) since I'm receive the result set as if there was no ORDER BY clause.

I need some help to get through this!

Here the version with DECODE (not working)
It is a Procedure with frstRow, nbRows and var_order as parameters
The output returns the rows as if no ORDER BY was used
-----
SELECT c1, c2
FROM
(
SELECT ROWNUM rn, arv.*
FROM A_AWA_AR arv
WHERE ROWNUM < (frstRow + nbRows - 1) -- show only some rows determined by procedure
ORDER BY DECODE(var_order, 1, c1, 2, c2, c1) -- sort by var_order
)
WHERE rn BETWEEN frstRow AND (frstRow + nbRows)
AND ROWNUM <= nbRows
-----

Here the version without DECODE (working)
The output returns the rows as expected - ordered by c2 column
-----
SELECT c1, c2
FROM
(
SELECT ROWNUM rn, arv.*
FROM A_AWA_AR arv
WHERE ROWNUM < (frstRow + nbRows - 1) -- show only some rows determined by procedure
ORDER BY c2 -- sort by var_order
)
WHERE rn BETWEEN frstRow AND (frstRow + nbRows)
AND ROWNUM <= nbRows
-----