Home » Developer & Programmer » Reports & Discoverer » order the list of value
order the list of value [message #472489] Sat, 21 August 2010 02:18 Go to next message
Amar_bu
Messages: 32
Registered: July 2010
Location: riyadh
Member
I am using oracle 6i and i have parameter. I want the value of parameter to be sorted i use the following query
SELECT DISTINCT To_Char(LD_TASK.TASK_ID) DISPLAY_COL1
FROM LD_TASK
UNION
SELECT '%' DISPLAY_COL1
FROM DUAL
order by display_Col1

In the output i get the value sorted but according to just the first number how i could make it according to the hole number

example of the output
1
11
12
13
14
6
7
8
9

Re: order the list of value [message #472494 is a reply to message #472489] Sat, 21 August 2010 03:04 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have many ways to workaround this:
1/ Use numbers and -1 (for instance) instead of '%' (and convert it to '%' in the interface if you want)
2/ Add '%' in the client program code not in the query
3/ Use an inline view (select * from (select task_id from ... order by trask_id) union all ...

By the way if task_id is the primary key, don't use DISTINCT.
Use UNION ALL and not UNION as I bet you have no task_id named '%'.

Here's one query that should work:
select decode(task_id, -1, '%', to_char(task_id)) display_col1
from ( select task_id from ld_task
       union all 
       select -1 from dual
       order by 1 )


(Note how it is formatted, Please read OraFAQ Forum Guide, especially "How to format your post?" section.)

Regards
Michel
Re: order the list of value [message #472496 is a reply to message #472494] Sat, 21 August 2010 03:25 Go to previous message
Amar_bu
Messages: 32
Registered: July 2010
Location: riyadh
Member
Thanks
Previous Topic: calling report thorugh DB Block
Next Topic: Removing spaces from top of report
Goto Forum:
  


Current Time: Sat Oct 25 21:11:28 CDT 2014

Total time taken to generate the page: 0.04096 seconds