Sum with REPLACE!

[Dulima-SP]

Environment information:
* Oracle version: 10 g
* Operating system: HR 4

Srs .. Good morning to all !!!

I have a peculiar problem !!

I have a query inside the application that makes a sum in a Varchar2 field
until there without error ...
But there is a difference

When I run this query in some development tool I need to put a replace in this sum

Select all

 
  -- O REPLACE VEM AQUI 
                       sum(replace(md.valor,'.',',')) valor, 
  -- FINAL DO REPLACE 
 
                       

more when I play this query with replace in the application and I command the error

Select all

                       sum(replace(md.valor,'.',',')) valor, 
                           * 
ERROR at line 93: 
ORA-01722: invalid number 

does anyone have any idea what it can be ???

[victorhugomuniz]

if I did not get me the replace returns a string dai can not apply the sum funcao

reverse the order .. make your sum and then apply the replace in your result

[victorhugomuniz]

I had not read everything ...

to apply sum you will have to give a to_number and then apply your sum then your replace

to_number ( '1210.73', '9999.99')

where the second parameter and the conversion mask

[Dulima-SP]

Dude, I read about 15 times ...

I guess I did not express myself bad !!


The replace works, more only in the tool with toad, on the server I have to remove the Replace !!!

What I did not understand ....


porque that in toad only works with the replace and the server only works when I Shoot the Replace ??

[victorhugomuniz]

Sorry for my ignorance but I think that sum in VARCHAR field does not funciona no
only will work if you do not have data to be displayed ...
maybe in your application has some clauses where that prevent the Data display


But from what I understood your problem and replace ne ..

this stop. And as decimal house separator is linked to regionality .. but it does not make sense to be different once the toad is looking for the same bank that your application

in the end .. Sorry if I am taking your time

[Dulima-SP]

Nothing ....

more this and the point if it is the same base, because it works differently ...
I read a few things about regionality, but did not take My doubts !!!

Will and configure the tool ???

porque does not make sense ....

the same thing rotate differently in the same bank !!!

[victorhugomuniz]

It can be something that is configured on toad ne .. perhaps the profile of the user ..
maybe the version of the toad does not support your version of Oracle ..

What version Toad?
What is Oracle Version?
What kind of field?
How is your application?

[Dulima-SP]

What version is toad?

9.0.0.160

What is Oracle Version?

10.2.0.4.0

What type of field?

varchar2

How and his application?

ASP language and VB script

You agree that strange .... ??

[victorhugomuniz]

the application of the application of the application and the same as you are using in toad?
Have you tried using another tool .. PLSQL Developer for example?

is strange ne .. there is something wrong that is not right..rsrsrs

[victorhugomuniz]

[url=http://www.bigresource.com/VB-Oracle-Up ... zd76Q.html]este and the reverse of your ..

[Dulima-SP]

The user and the same ...


I have already rotated in the developer .... even in diferent machines and nothing ...

same mistake..

[victorhugomuniz]

Select all

 
ORA-01722:	invalid number 
Cause:	The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates. 
Action:	Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.  

Analyzing the corrective action we have

Check the character chains in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "and" or "and" and "and re-cleanse the operation.


Then ..
When you replace your point decimal separator for virgula la no toad he does not understand Virgula by decimal home separator soon you have the error of Invalid Number
point (.) is the American and virgulum (,) "Brazilian" separator then it is very likely that something should be set on toad .. already gave a good funciation ??

[dr_gori]

Probably the Toad should be session.
See this topic on the format: http://en.glufke.net/oracle/viewtopic.php?t=5253
In Toad, to change the format is in the View / Toad Options menu
from there goes in general
There are decimal or decimal point.

[Dulima-SP]

Ball show ...
worked ... from the deck ...


vlw Even ....

ABC