Dynamic order by without dynamic sql ? 
Author Message
 Dynamic order by without dynamic sql ?

I have  a huge select statement cursor that's a union. Sorting of the
cursor resultset should be dynamic (ie order by column comes into the
proc as input parameter). Usually I do this as decode. eg

select
customer_no, customer_name, address, phone etc
from
where
order by
decode(i_var, 'customer_no', 1,
                    'customer_name', 2)

This works fine as long as statment is NOT UNION. When it's Union, plsql
complains and expects order by to be ONLY COLUMN NUMBER (and doesn't
allow decode, function etc).

Anybody has any suggestion on how this can be done ? I want to avoid
dynamic sql only because I think it makes code clumsy (putting the whole
giant sql statement in a variable etc) rather than simply opening up the
cursor.

Any ideas ? Is there a way of sorting pl*sql table type variable  (if
this works then I can get cursor data without order by and then order it
by sorting before returning the resultset to caller VB program.

Rdbms version is 7.3.4.

Sameer Utrankar



Sat, 20 Jul 2002 03:00:00 GMT
 Dynamic order by without dynamic sql ?


Quote:
> I have  a huge select statement cursor that's a union. Sorting of the
> cursor resultset should be dynamic (ie order by column comes into the
> proc as input parameter). Usually I do this as decode. eg

> select
> customer_no, customer_name, address, phone etc
> from
> where
> order by
> decode(i_var, 'customer_no', 1,
>                     'customer_name', 2)

> This works fine as long as statment is NOT UNION. When it's Union, plsql
> complains and expects order by to be ONLY COLUMN NUMBER (and doesn't
> allow decode, function etc).

You should move your query into subquery.
E.g:
select *
from
 (Origin query with set operators) sub
order by col_name
--
Alex P. Zotov


Sun, 21 Jul 2002 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Conditional ORDER BY without dynamic SQL?

2. Dynamic SQL accessing dynamic temp tables

3. dynamic T-SQL for dynamic parameter

4. Dynamic Execute Statement vs. Passing Dynamic SQL

5. DYNAMIC SQL MVS DB2 V6 DYNAMIC WHERE

6. WHERE IN(@stringlist) without dynamic SQL?

7. Dynamic SQL without sp_executesql

8. ODBC Dynamic table creation without sql statement

9. Dynamic SQL - (order by)

10. X/Open Dynamic SQL, Ordering the spec

11. Dynamic package isn't dynamic in dtsrun

12. Dynamic Properties & Dynamic Sources/Destinations


 
Powered by phpBB® Forum Software