using order by in the parameter form in reports

[bilalch]

i've a field named 'PF2' in the parameter form and it's source is a user parameter named 'ORD' having static lov
and in the query i've writen as.

select
from
where
order by :ORD

but it doesn't generate the required results i.e. not giving in that order.

2ndly in the static lov i'm giving as TABLE.COLUMN
can i mask it with some name like DATE etc. not in the DB.
i'm using oracle 8 with developer 2000.

 

[lewisp]

I think you may have to use [tt]
select
from
where
order by &ORD[/tt]

....but I'm no report expert.

 

[sem]

Lewisp, I'm also, but know it for sure

Bilalch,
besides passing lexical instead of bind variable, you should apply valid default value, because query syntax should be valid during design time.

Of course, if you're asking about Reports.

Regards, Dima

 

[bilalch]

hi dear frends (Lewisp & Dima)

as for the lexicals are concerned the query doesn't allow.
and returns an error
ora-936 missing expression
==>

ok Dima

but i want to specify the order at run time with three options and that's the only way i thought i can do it.
also it doesnt generate any error but only it doesn't work.

thanx

 

[lewisp]

As Dima said, using a lexical parameter means the parameter must have a default value assigned at design-time that makes the query valid.

So for example, to allow your SQL to work you might assign a default value of '1' to the &ORD parameter. Or you might write the SQL like this

[tt]select something
from a_table
where a_column = something
&ORD[/tt]

...in which case the ORD parameter wouldnt need a default value. However, when you run the report you might assign a string to the parameter that looks like this

[tt]'ORDER BY a_column'[/tt]

 

[bilalch]

hi dear lewisp

many thanx for ur response to my quetions.
the problem is that in that case i've to use the whole order by clause as a lexical.
2ndly i've a column named cust_id, but i would like it to be displayed as only " CUSTOMER " is there any format mask that can show it like this.

thanx ,, bye