| Order By Numeric value for a Varchar2 column [message #402972] |
Wed, 13 May 2009 05:40  |
aaashwini@gmail.com
Messages: 24
Registered: May 2009
|
Junior Member |
|
|
Hi,
I have a custom report, in which task number and task name is one column concatenated with a space. Like "1400 ATL".
This value needs to be ordered numerically. Ordering is happening in Toad when I run the query but when I run the report, report's output does not order numerically, its doing alphabetically. There is no other query in the data model of the report.
Please help.
Thanks
|
|
|
|
|
|
| Re: Order By Numeric value for a Varchar2 column [message #402980 is a reply to message #402972] |
Wed, 13 May 2009 05:48  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Do you have an ORDER BY clause in your query?
If not, then the data will come back in any order that Oracle likes the look of.
So, based on the sketchy details that you provide, you need to extract the numeric part of your code, and order by that.
Further help is contingent on the provision of more details, such as test data.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Order By Numeric value for a Varchar2 column [message #403046 is a reply to message #402972] |
Wed, 13 May 2009 10:00 |
aaashwini@gmail.com
Messages: 24
Registered: May 2009
|
Junior Member |
|
|
The snippet of my query is below: I'm trying to do a order by for the column: task_number || ' ' ||task_name. It is howing desired result when I run in Toad.
Query:
select /*(substr(task_number || ' ' ||task_name,1,instr(task_number || ' ' ||task_name,' ')-1)),*/
distinct task_number || ' ' ||task_name
from pa_tasks
where task_number in ('1400','1401','1402','1403')
order by task_number || ' ' ||task_name
Output: TASK_NUMBER||''||TASK_NAME
1400 ATL
1401 BTL
1402 POST PRODUCTION
1403 OTHER
This output is required and it comes as desired when I just run the query in Toad but when the same query is embedded in the report 10G and run as a concurrent program. The report output comes as :
1400 ATL
1401 BTL
1403 OTHER
1402 POST PRODUCTION
which is an alphabetical sorting.
|
|
|
|
|
|
|
|
| Re: Order By Numeric value for a Varchar2 column [message #403052 is a reply to message #402972] |
Wed, 13 May 2009 10:15 |
aaashwini@gmail.com
Messages: 24
Registered: May 2009
|
Junior Member |
|
|
The snippet of my query is below: I'm trying to do a order by for the column: task_number || ' ' ||task_name. It is howing desired result when I run in Toad.
Query:
select /*(substr(task_number || ' ' ||task_name,1,instr(task_number || ' ' ||task_name,' ')-1)),*/
distinct task_number || ' ' ||task_name
from pa_tasks
where task_number in ('1400','1401','1402','1403')
order by task_number || ' ' ||task_name
Output: TASK_NUMBER||''||TASK_NAME
1400 ATL
1401 BTL
1402 POST PRODUCTION
1403 OTHER
This output is required and it comes as desired when I just run the query in Toad but when the same query is embedded in the report 10G and run as a concurrent program. The report output comes as :
1400 ATL
1401 BTL
1403 OTHER
1402 POST PRODUCTION
which is an alphabetical sorting.
Both the Task Number and task name are varchar2 columns and the specifiction of the report requires both the columns to be concatenated. I have tried adding separate column of task number and an order by only for that column but in vain....
|
|
|
|
|
|
| Re: Order By Numeric value for a Varchar2 column [message #403057 is a reply to message #403051] |
Wed, 13 May 2009 10:29 |
joicejohn
Messages: 327
Registered: March 2008
Location: India
|
Senior Member |
|
|
| JRowbottom wrote on Wed, 13 May 2009 20:45 | Technically, the Report is right.
task_number||' '||task_name is a string, and should be sorted as a string.
|
SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 13 20:49:38 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> WITH test_tab AS
2 (SELECT 1400 task_number, 'ATL' task_name
3 FROM DUAL
4 UNION ALL
5 SELECT 1401, 'BTL'
6 FROM DUAL
7 UNION ALL
8 SELECT 1403, 'OTHER'
9 FROM DUAL
10 UNION ALL
11 SELECT 1402, 'POST PRODUCTION'
12 FROM DUAL)
13 SELECT *
14 FROM test_tab
15 ORDER BY task_number || ' ' || task_name;
TASK_NUMBER TASK_NAME
----------- ---------------
1400 ATL
1401 BTL
1402 POST PRODUCTION
1403 OTHER
SQL>
The Report should give the same order if the ORDER BY Clause is in place. I mean technically the string '1403 OTHER' is greater than '1402 POST PRODUCTION'. Then why in OP's case (if he has supplied the same ORDER BY Clause) is he getting it otherwise. Might be the reporting tool is causing this issue. I am not familiar with Oracle Report Builder.
Regards,
Jo
[Updated on: Wed, 13 May 2009 11:08] Report message to a moderator |
|
|
|
|
|
| Re: Order By Numeric value for a Varchar2 column [message #403062 is a reply to message #402972] |
Wed, 13 May 2009 10:37 |
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I suspect the problem is nothing to do with the SQL and everything to do with the report.
Have you got break order set on any of the query columns?
There will be an arrow (up or down) between the datatype graphic and the column name in the data model layout.
|
|
|
|
|
|
| Re: Order By Numeric value for a Varchar2 column [message #403067 is a reply to message #403052] |
Wed, 13 May 2009 10:47 |
c_stenersen
Messages: 255
Registered: August 2007
|
Senior Member |
|
|
Well, if task_number is a string then it will of course be sorted as a string. You'll need to use to_number on it if you want to order it numerically. But why do you store it as a varchar2 if you're anyways always going to insert numbers into it? (You can concatenate a number with a varchar2 if that's what you're worried about.)
That the report should show both columns concatenated doesn't mean that you need to order them by the concatenated value. I would make that task_number column a number, and simply order by task_number, task_name.
As JRowbottom and cookiemonster has said earlier here, your tool is probably re-ordering it. But you should anyways order by a number, because you could risk that the values are for instance '852' and '1403', making alphabetical sorting wrong.
|
|
|
|
|
|
| Re: Order By Numeric value for a Varchar2 column [message #403081 is a reply to message #402972] |
Wed, 13 May 2009 11:19 |
aaashwini@gmail.com
Messages: 24
Registered: May 2009
|
Junior Member |
|
|
Thanks all of you the kind help bestowed...
There was a break-order set in the report which was over-riding the sort mentioned in the report. I just removed the break-order in property pallete of the column TASK_NUMBER||' '||TASK_NAME and added an ascending order for my new column Task Number and it worked. Many Thanks.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
