Re: mixing dynamic native sql and static sql?

Consider constructing your clauses as strings in VARCHAR2s and then putting it all together and running it as native dynamic SQL. I have done this numerous times. It is not exactly elegant but it gets the job done. The DECODE can be used to create the string that contains the ORDER BY clause.

Daniel Morgan

Peter Mutsaers wrote:

> >> "John" == John Russell <netnews2_at_johnrussell.mailshell.com> writes:
>
> John> You can use something like
>
> John> ORDER BY DECODE(test_expression, case1, column1, case2, column2,
> John> case3, column3, ...)
>
> Yes, but in my case, I have to order on multiple columns (for some
> inputs) and some of the coumns are asc, some are desc. Seems quite
> impossible with the order by decode construct.
>
> If only I could do it in two steps, first creating a ref cursor using
> a static query, then using a small dynamic query that uses this ref
> cursor and adds the (dynamic) order by stuff.
>
> --
> Peter Mutsaers | Dübendorf | UNIX - Live free or die
> plm_at_gmx.li | Switzerland | Sent via FreeBSD 4.5
Received on Sun Apr 14 2002 - 18:36:33 CDT