OraFAQ Forum: Training & Certification » convert columns to numbers and calculate % (merged 3 threads)

Home » Other » Training & Certification » convert columns to numbers and calculate % (merged 3 threads)

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

convert columns to numbers and calculate % (merged 3 threads) [message #279333]

Wed, 07 November 2007 18:45

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--

Report message to a moderator

Re: convert columns to numbers and calculate % [message #279336 is a reply to message #279333]

Wed, 07 November 2007 18:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/t/88153/0/
Read & FOLLOW posting guidelines

Report message to a moderator

Re: convert columns to numbers and calculate % [message #279339 is a reply to message #279333]

Wed, 07 November 2007 18:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

do NOT cross/multipost

Report message to a moderator

Re: convert columns to numbers and calculate % [message #279344 is a reply to message #279333]

Wed, 07 November 2007 19:49

rleishman
Messages: 3728
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

Report message to a moderator

Re: convert columns to numbers and calculate % [message #279345 is a reply to message #279333]

Wed, 07 November 2007 19:50

BlackSwan
Messages: 26766
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".

Report message to a moderator

Re: convert columns to numbers and calculate % [message #279462 is a reply to message #279344]

Thu, 08 November 2007 10:45

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

Report message to a moderator

Re: convert columns to numbers and calculate % [message #279472 is a reply to message #279462]

Thu, 08 November 2007 11:52

Frank
Messages: 7901
Registered: March 2000

Senior Member

SQL Server??

Did you notice the name of this site?

Report message to a moderator

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: 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

Report message to a moderator

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: 26766
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

Report message to a moderator

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: 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

Report message to a moderator

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: 26766
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.

Report message to a moderator

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: 7901
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?

Report message to a moderator

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: 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

Report message to a moderator

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: 68645
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

Report message to a moderator

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: 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

Report message to a moderator

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: 68645
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

Report message to a moderator

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: 26766
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

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.

Report message to a moderator

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: 26766
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

Report message to a moderator

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: 14
Registered: November 2007

Junior Member

None of the rows contain numeric data. I wanted to see if I could convert it number.

Report message to a moderator

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: 26766
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?

Report message to a moderator

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: 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.

Report message to a moderator

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: 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.

Report message to a moderator

Previous Topic:	Oracle Scope
Next Topic:	Help with triggers

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 07:23:14 CDT 2024