Home » Other » Training & Certification » convert columns to numbers and calculate % (merged 3 threads)
convert columns to numbers and calculate % (merged 3 threads) [message #279333] Wed, 07 November 2007 18:45 Go to next message
labyrinth248
Messages: 14
Registered: November 2007
Junior Member
hey everyone,
I am sort of new to plsql so I have a question here. I have 3 columns, the first column is a date column, the second and third columns are varchar. I need to convert the second and third columns to numbers that also count for each specific date in the first column. After this is done, I need to calculate a percentage from the second and third columns to show in a 4th column.

Thanks--
Re: convert columns to numbers and calculate % [message #279336 is a reply to message #279333] Wed, 07 November 2007 18:56 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Read & FOLLOW posting guidelines
Re: convert columns to numbers and calculate % [message #279339 is a reply to message #279333] Wed, 07 November 2007 18:56 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
do NOT cross/multipost
Re: convert columns to numbers and calculate % [message #279344 is a reply to message #279333] Wed, 07 November 2007 19:49 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
select col1, to_number(col2), to_number(col3), to_char(100*to_number(col2)/to_number(col2), '990.99%')
from my_table


If this isn't exactly what you wanted, consider that perhaps you could've provided a little more information. Try reading the Forum Posting Guidelines first though.

Ross Leishman
Re: convert columns to numbers and calculate % [message #279345 is a reply to message #279333] Wed, 07 November 2007 19:50 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Do NOT cross/multipost!
It appears you are a SLOW learner.
You were provided posting guidelines that you continue to ignore.

SEARCH this forum for "pivot query".
Re: convert columns to numbers and calculate % [message #279462 is a reply to message #279344] Thu, 08 November 2007 10:45 Go to previous messageGo to next message
labyrinth248
Messages: 14
Registered: November 2007
Junior Member
Thanks for your help Ross.

I am trying to do this in reporting services in Sql server business intelligence 2005.

I just want to take ‘dol.leak_origin_type’ and 'dol.priority’ and convert them to numbers. Then get a percent of those two columns for a new column. ‘dol.leak_origin_type’, and 'dol.priority’ will be counted based on the ‘dol.updated_dt’ column.
I am getting an invalid number error.
Here is what I have so far:

select distinct dol.updated_dt, to_number(dol.leak_origin_type), to_number(dol.priority), to_char(100*to_number(dol.leak_origin_type)/to_number(dol.leak_origin_type), '990.99%')
from dist_ord_leak_details_hdr_hst dol


Let me know if you need any more information.

Thanks
Re: convert columns to numbers and calculate % [message #279472 is a reply to message #279462] Thu, 08 November 2007 11:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
SQL Server??

Did you notice the name of this site?
Re: convert columns to numbers and calculate % (merged 3 threads) [message #279524 is a reply to message #279333] Thu, 08 November 2007 19:44 Go to previous messageGo to next message
labyrinth248
Messages: 14
Registered: November 2007
Junior Member
Im trying to do this in toad with oracle version 9i (9.2.0.6.0)

Can anyone help with this?

Thanks
Re: convert columns to numbers and calculate % (merged 3 threads) [message #279525 is a reply to message #279333] Thu, 08 November 2007 19:56 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
If you used SQL*Plus, it would indicate where the invalid number occurred.
Does the SQL below generate any error?
select distinct dol.updated_dt, to_number(dol.leak_origin_type), to_number(dol.priority
from dist_ord_leak_details_hdr_hst dol


Re: convert columns to numbers and calculate % (merged 3 threads) [message #279527 is a reply to message #279525] Thu, 08 November 2007 20:04 Go to previous messageGo to next message
labyrinth248
Messages: 14
Registered: November 2007
Junior Member
I remember seeing an invalid number error. I am going to check again tomorrow at work to see what he error says and if it says where it is. I am testing it in toad.

I'll get back to this tomorrow morning.

Thanks
Re: convert columns to numbers and calculate % (merged 3 threads) [message #279531 is a reply to message #279333] Thu, 08 November 2007 22:17 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>I remember seeing an invalid number error.
If this is true, then you have non-numeric data in the table.
Re: convert columns to numbers and calculate % (merged 3 threads) [message #279590 is a reply to message #279524] Fri, 09 November 2007 04:36 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
labyrinth248 wrote on Fri, 09 November 2007 02:44

Im trying to do this in toad with oracle version 9i (9.2.0.6.0)

Can anyone help with this?

Thanks

labyrinth248 wrote one comment before that

I am trying to do this in reporting services in Sql server business intelligence 2005.


Now which of the two is it?
Re: convert columns to numbers and calculate % (merged 3 threads) [message #279626 is a reply to message #279590] Fri, 09 November 2007 07:37 Go to previous messageGo to next message
labyrinth248
Messages: 14
Registered: November 2007
Junior Member
I am using both reporting services and toad. I test the code in toad then move it over to reporting services where I build the report that uses an oracle data source. For now I just want it to work in toad. When I execute the following:

---------------------------------------------------
select dol.updated_dt, to_number(dol.leak_origin_type),
to_number(dol.priority),
to_char(100*to_number(dol.leak_origin_type)/to_number(dol.leak_origin_type), '990.99%'),
dor.act_result_leak_invest
from dist_ord_leak_details_hdr_hst dol, dist_ord_rslt_leak_invest_hdr dor
-------------------------------------------------------


I get the toad error: ORA-01722: invalid number
What should I do to convert them to numbers.
Thanks
Re: convert columns to numbers and calculate % (merged 3 threads) [message #279651 is a reply to message #279626] Fri, 09 November 2007 09:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

What should I do to convert them to numbers

If it is not a valid number then you can't convert it to a number.

But the way:
100*to_number(dol.leak_origin_type)/to_number(dol.leak_origin_type)
Always returns 100 (except for 0 and not number data).

Regards
Michel
Re: convert columns to numbers and calculate % (merged 3 threads) [message #279660 is a reply to message #279333] Fri, 09 November 2007 09:17 Go to previous messageGo to next message
labyrinth248
Messages: 14
Registered: November 2007
Junior Member
Thanks for the response.
Is it possible to convert a varchar2 data type to a number
data type in a select statement?

-Thanks
Re: convert columns to numbers and calculate % (merged 3 threads) [message #279667 is a reply to message #279660] Fri, 09 November 2007 09:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Is it possible to convert a varchar2 data type to a number

Yes use TO_NUMBER but only if it is a real number.

Regards
Michel
Re: convert columns to numbers and calculate % (merged 3 threads) [message #279669 is a reply to message #279333] Fri, 09 November 2007 09:35 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
What part of "If it is not a valid number then you can't convert it to a number." do you NOT understand?

To what number should 'A' "converted"?

Does the SQL below generate any error?
select distinct dol.updated_dt, to_number(dol.leak_origin_type), to_number(dol.priority
from dist_ord_leak_details_hdr_hst dol


[Updated on: Fri, 09 November 2007 09:38] by Moderator

Report message to a moderator

Re: convert columns to numbers and calculate % (merged 3 threads) [message #279674 is a reply to message #279333] Fri, 09 November 2007 09:54 Go to previous messageGo to next message
labyrinth248
Messages: 14
Registered: November 2007
Junior Member
Yes, that query gets the same error as mentioned
before. I will figure out a new way to do it.

Thanks everyone for you help.
Re: convert columns to numbers and calculate % (merged 3 threads) [message #279677 is a reply to message #279333] Fri, 09 November 2007 09:57 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
> I will figure out a new way to do it.
or just delete the rows which do not contain actual numeric data
Re: convert columns to numbers and calculate % (merged 3 threads) [message #279679 is a reply to message #279333] Fri, 09 November 2007 10:05 Go to previous messageGo to next message
labyrinth248
Messages: 14
Registered: November 2007
Junior Member
None of the rows contain numeric data. I wanted to see if I could convert it number.
Re: convert columns to numbers and calculate % (merged 3 threads) [message #279681 is a reply to message #279333] Fri, 09 November 2007 10:11 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>None of the rows contain numeric data. I wanted to see if I could convert it number.
HUH?
To what number does 'This is a random string.' become?
Re: convert columns to numbers and calculate % (merged 3 threads) [message #279687 is a reply to message #279333] Fri, 09 November 2007 10:57 Go to previous messageGo to next message
labyrinth248
Messages: 14
Registered: November 2007
Junior Member
I’m sorry for all of the confusion on this topic.

select distinct to_char(dol.updated_dt, 'DD') as "date", 
to_number(dol.leak_origin_type), to_number(dol.priority), 
to _char(100*to_number(dol.leak_origin_type)/to_number(dol.leak_origin_type), '990.99%'),
dor.act_result_leak_invest
from dist_ord_leak_details_hdr_hst dol,
dist_ord_rslt_leak_invest_hdr dor 


dol.updated_dt is a date data type. dol.leak_origin_type is a varchar2 data type. dol.priority is a varchar2 data type. The data that is in the dol.leak_origin_type column only consist of “Leaks”. The only data that is in the dol.priority column consists of “Immediate”. What I want to do is convert dol.leak_origin_type and dol.priority to a number say for example ‘1’, that counts for each occurrence on dol.updated_dt.

So, whenever there is a “Leaks”, or “Immediate” on a certain date, it will add to the count on that date.
Re: convert columns to numbers and calculate % (merged 3 threads) [message #279691 is a reply to message #279333] Fri, 09 November 2007 11:46 Go to previous message
labyrinth248
Messages: 14
Registered: November 2007
Junior Member
I think i figured out how to do it. I was making
it more difficult than it had to be.
I can just decode the varchar columns to "1" and then do a
count. The only thing now is to get the percent of the
two columns that I decode. As you can see I am
new to this language. Let me know if there is a better
way.
Previous Topic: Oracle Scope
Next Topic: Help with triggers
Goto Forum:
  


Current Time: Fri Dec 09 05:43:36 CST 2016

Total time taken to generate the page: 0.07887 seconds