OraFAQ Forum: Reports & Discoverer » order the list of value

Home » Developer & Programmer » Reports & Discoverer » order the list of value

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

order the list of value [message #472489]

Sat, 21 August 2010 02:18

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

Report message to a moderator

Re: order the list of value [message #472494 is a reply to message #472489]

Sat, 21 August 2010 03:04

Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: order the list of value [message #472496 is a reply to message #472494]

Sat, 21 August 2010 03:25

Amar_bu
Messages: 32
Registered: July 2010
Location: riyadh

Member

Thanks

Report message to a moderator

Previous Topic:	calling report thorugh DB Block
Next Topic:	Removing spaces from top of report

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 09 22:11:05 CST 2024