Home » Developer & Programmer » Reports & Discoverer » Order By Numeric value for a Varchar2 column (Oracle 10G, Reprots Builder 10G)
Order By Numeric value for a Varchar2 column [message #402972] Wed, 13 May 2009 05:40 Go to next message
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 #402975 is a reply to message #402972] Wed, 13 May 2009 05:43 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
I am not familiar with reports, but, I think, to_number function would solve your problem

regards,
Delna
Re: Order By Numeric value for a Varchar2 column [message #402980 is a reply to message #402972] Wed, 13 May 2009 05:48 Go to previous messageGo to next message
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 #402982 is a reply to message #402972] Wed, 13 May 2009 05:50 Go to previous messageGo to next message
cookiemonster
Messages: 12410
Registered: September 2008
Location: Rainy Manchester
Senior Member
That'll give you invalid number.

@aaashwini@gmail.com

More than one example would help.
A full test case (with create tables and insert statements) would help even more.

But if you can lpad your numbers with 0's so they're all the same length that should sort it.
Re: Order By Numeric value for a Varchar2 column [message #403005 is a reply to message #402972] Wed, 13 May 2009 07:43 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
If these fields are always numbers followed by a space and then the rest of the data, a simple substr should do it, based on an instr to find the space. If the number isn't always followed by a space you can use regexp_substr to find the number part of the field.
Do a to_number on the result, and order by that.

http://www.regular-expressions.info/reference.html
http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/rischert_regexp_pt2.html#t8

[Updated on: Wed, 13 May 2009 07:46]

Report message to a moderator

Re: Order By Numeric value for a Varchar2 column [message #403023 is a reply to message #403005] Wed, 13 May 2009 09:18 Go to previous messageGo to next message
aaashwini@gmail.com
Messages: 24
Registered: May 2009
Junior Member
I have tried removing the space and did order by only for the number part. It works as desired when I run in Toad but when the same query is posted in Report Builder ...the report output is not sorted as desired.....In the report there is one query in the data model...
Re: Order By Numeric value for a Varchar2 column [message #403032 is a reply to message #403023] Wed, 13 May 2009 09:27 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@aaashwini@gmail.com,

Can you please post the query and the results by running the query in SQL Plus. In case of ordering the results, don't depend on GUI's like Toad.

Also, please go through OraFAQ Forum Guide.

Regards,
Jo
Re: Order By Numeric value for a Varchar2 column [message #403046 is a reply to message #402972] Wed, 13 May 2009 10:00 Go to previous messageGo to next message
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 #403048 is a reply to message #403046] Wed, 13 May 2009 10:09 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@aaashwini@gmail.com,

Hmm.. If you have task_number as a seperate column and if task_number is in number data type and if you wan't it to be ordered by task_number why don't you just give

    ORDER BY task_number 


Does the query which has been embedded in the report have the same ORDER BY Clause?

Regards,
Jo
Re: Order By Numeric value for a Varchar2 column [message #403051 is a reply to message #403046] Wed, 13 May 2009 10:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Technically, the Report is right.
task_number||' '||task_name is a string, and should be sorted as a string.

What you want is this:
order by task_number,task_name
Re: Order By Numeric value for a Varchar2 column [message #403052 is a reply to message #402972] Wed, 13 May 2009 10:15 Go to previous messageGo to next message
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 #403055 is a reply to message #402972] Wed, 13 May 2009 10:26 Go to previous messageGo to next message
aaashwini@gmail.com
Messages: 24
Registered: May 2009
Junior Member
I have tried now : adding Task Number and Task Name as separate columns and did order by like : Order By Task_number, task_name but no luck in the output....
Re: Order By Numeric value for a Varchar2 column [message #403057 is a reply to message #403051] Wed, 13 May 2009 10:29 Go to previous messageGo to next message
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 #403060 is a reply to message #403057] Wed, 13 May 2009 10:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Good point - that is the correct order.

I guess that whatever reporting tool the OP is using is re-ordering his data after it's fetched from the database.
Re: Order By Numeric value for a Varchar2 column [message #403062 is a reply to message #402972] Wed, 13 May 2009 10:37 Go to previous messageGo to next message
cookiemonster
Messages: 12410
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 #403066 is a reply to message #402972] Wed, 13 May 2009 10:45 Go to previous messageGo to next message
aaashwini@gmail.com
Messages: 24
Registered: May 2009
Junior Member
Yes, there is a break order set for each different task_number||' '||task_name combination. How do I override this.
Re: Order By Numeric value for a Varchar2 column [message #403067 is a reply to message #403052] Wed, 13 May 2009 10:47 Go to previous messageGo to next message
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 #403070 is a reply to message #403066] Wed, 13 May 2009 10:56 Go to previous messageGo to next message
cookiemonster
Messages: 12410
Registered: September 2008
Location: Rainy Manchester
Senior Member
aaashwini@gmail.com wrote on Wed, 13 May 2009 16:45
Yes, there is a break order set for each different task_number||' '||task_name combination. How do I override this.


Open up the property palette and change it.
Suggest you also read up on what break order does in report builder help.

EDIT:typo

[Updated on: Wed, 13 May 2009 10:57]

Report message to a moderator

Re: Order By Numeric value for a Varchar2 column [message #403081 is a reply to message #402972] Wed, 13 May 2009 11:19 Go to previous message
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.
Previous Topic: run 100 report server
Next Topic: Discoverer Security
Goto Forum:
  


Current Time: Wed Dec 07 06:44:31 CST 2016

Total time taken to generate the page: 0.22449 seconds