Home » Other » Training & Certification » convert columns to numbers and calculate % (merged 3 threads)
convert columns to numbers and calculate % (merged 3 threads) Wed, 07 November 2007 18:45
 labyrinth248 Messages: 14Registered: 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
 BlackSwan Messages: 25574Registered: January 2009 Location: SoCal Senior Member
http://www.orafaq.com/forum/t/88153/0/
Re: convert columns to numbers and calculate % [message #279339 is a reply to message #279333] Wed, 07 November 2007 18:56
 BlackSwan Messages: 25574Registered: 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
 rleishman Messages: 3727Registered: 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
 BlackSwan Messages: 25574Registered: 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
 labyrinth248 Messages: 14Registered: November 2007 Junior Member

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

Thanks
Re: convert columns to numbers and calculate % [message #279472 is a reply to message #279462] Thu, 08 November 2007 11:52
 Frank Messages: 7880Registered: 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
 labyrinth248 Messages: 14Registered: 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
 BlackSwan Messages: 25574Registered: 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
 labyrinth248 Messages: 14Registered: 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
 BlackSwan Messages: 25574Registered: 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
 Frank Messages: 7880Registered: 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
 labyrinth248 Messages: 14Registered: 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
 Michel Cadot Messages: 65146Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 labyrinth248 Messages: 14Registered: 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
 Michel Cadot Messages: 65146Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 BlackSwan Messages: 25574Registered: 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
 labyrinth248 Messages: 14Registered: 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
 BlackSwan Messages: 25574Registered: 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
 labyrinth248 Messages: 14Registered: 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
 BlackSwan Messages: 25574Registered: 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
 labyrinth248 Messages: 14Registered: 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
 labyrinth248 Messages: 14Registered: 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: selecting a best combination of numbers from a list of numbers Next Topic: tables and users
Goto Forum:

Current Time: Mon Aug 21 05:18:57 CDT 2017

Total time taken to generate the page: 0.09182 seconds