Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Writing order by clause with user parameter

153383Nov 14 2002
I have a user parameter where the user is supposed to choose what column they want to sort by.
And they can choose between k.personnr and k.personnavn. I am trying to do this with a decode function.
Now this would work fine if there was no union between these select statements.
But with the union I get a ORA-01785, where they tell me to put in a number.

Here is the sql:

SELECT
k.personnr as PERSON ,
k.personnavn as NAVN,
r.belop as SUM,
concat(to_char(r.bilagdato,'MM'), to_char(r.bilagdato,'YYYY')) AS MND,
PERIODE(concat(to_char(r.bilagdato,'MM'), to_char(r.bilagdato,'YYYY'))) AS PERIODE
FROM
reskontro r,
klient k,
kontoplan p,
konto c
WHERE
r.distriktnr='21'
and r.distriktnr=c.kontoid
and r.distriktnr=k.distriktnr
and r.personnr=k.personnr
and r.kontonr=p.kontonr
and (((r.bilagdato between :p_fra_dato and :p_til_dato) and (:p_fra_dato <= c.overfort) and (:p_til_dato <= c.overfort))
or ((r.bilagdato between :p_fra_dato and c.overfort) and (:p_fra_dato <= c.overfort) and (:p_til_dato >= c.overfort)))
UNION ALL
SELECT
k.personnr as PERSON ,
k.personnavn as NAVN,
(t.belop/100) as SUM,
concat(to_char(t.bilagdato,'MM'), to_char(t.bilagdato,'YYYY')) AS MND,
PERIODE(concat(to_char(t.bilagdato,'MM'), to_char(t.bilagdato,'YYYY')),:P_HORISONTAL) AS PERIODE
FROM
transaksjon t,
klient k,
kontoplan p
WHERE
t.distriktnr='21'
and t.distriktnr=k.distriktnr
and t.personnr=k.personnr
and t.kontonr=p.kontonr
and t.bilagdato between :p_fra_dato and :p_til_dato

This is the order by clause I would like to use:
order by decode(:p_order_by,'Personnavn',k.personnavn,k.personnr)

I also get an error when I use:
order by decode(:p_order_by,'Personnavn',2,1)

Can anybody tell me how to work around this problem?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 12 2002
Added on Nov 14 2002
5 comments
176 views