Re: New problem with Firebird dialect 3

Geoff.

it is solved. I had to change the SQL script (domains)  that created the fields.
I changed a number of numeric datatypes to float and it is working now. Well. I
know now how to migrate from Dialect 1 to Dialect 3. Hard Work!

Thanks a lot to you and André for all the help.
When any of you come to Peru... let me know please... You have a friend here





 
> -- 
> Geoff Wass [dBVIPS]
> Montréal, Québec, Canada
> 
> .|.|.|        dBASE info at http://geocities.com/geoff_wass       |.|.|.
> .|.|.| ---------------------------------------------------------- |.|.|.
> .|.|.|             IT Consultant http://Geoff_Wass.com            |.|.|.

In article <$I6gaQ#eIHA.1880@news-server>, jaime.escobal@laviga.com 
says...
> Geoff.
> 
> it is solved. I had to change the SQL script (domains)  that created the
fields. I changed a number of numeric datatypes to float and it is working now.
Well. I know now how to migrate from Dialect 1 to Dialect 3. Hard Work!
> 
> Thanks a lot to you and André for all the help.
> When any of you come to Peru... let me know please... You have a friend
here


Jaime,

It is good to hear you got this solved. Thanks for the success report.

I love travelling and would like to visit everywhere, including Peru. I 
just need to win the lottery...

-- 
Geoff Wass [dBVIPS]
Montréal, Québec, Canada

.|.|.|        dBASE info at http://geocities.com/geoff_wass       |.|.|.
.|.|.| ---------------------------------------------------------- |.|.|.

jaime Escobal schrieb:
> I changed a number of numeric datatypes to float and it is working now.

I am sorry to blur your bright sky a bit :)
But the problem must have been something else, even if it is working now.

Did the error only happen in stored procedures?
How did you "create" the new database, all completely new from
scripts?

Because when restoring data from a backup, you would probably also need 
to re-compile your stored procedures.


Numeric types work very well in Firebird. Maybe your precision and scale 
have not been set correctly.

You have to define a field as "numeric(p/s)" where p = precision and s
= 
scale.  Note that precision is a number of _BITS_. Being used to dBase, 
one could easily mix that up with "digits".

For all monetary calculations, I would definitely recommend using a 
precision of 15, and depending on how exact you want to have a 
calculation, a scale of 2 or 4

I have all my monetary fields in either numeric(15,4) or decimal(15,4) 
(which are nearly the same datatypes).

For measures in cm, km, m³ and such I am using numeric(15,2)


You CAN use floats and doubles, instead.
They can even take much bigger numbers and calculating operations work 
faster than with numerics and decimals.
But you will get rounding problems later.

One more thing to know about numerics, very important:
the decimal places in calculations with numerics will be "added".

if you have two numerics, one 11,3 and one 15,2 the result will be a new 
numeric 15,5.

Lysander Wrote:

> if you have two numerics, one 11,3 and one 15,2 the result will be a new
numeric 15,5.

André,
I believe this is the probem.  I found the line in the stored procedure(s) that
give the error. an example of the variables, domains and values are:

nQuantity  ---> NUMERIC(18,7)  : 1,000
nPrice       ---> NUMERIC(18,7)  : 800
nTotal      ----> NUMERIC(18,2) :  (result)

this is the line that causes the error:

nTotal = ROUND( nQuantity, nPrice, 2) 

But I need the total to have only 2 decimals... Is there any way of doing this?

Lysander Wrote:

> Round() is not a Firebird function. If you have it in your stored proc 
> code, it means that you are using a User Defined Function ???
> If yes, you must of course also update the UDF library to a more actual 
> one, because the one you are using could be based on OLD numerical 
> datatypes.

Yes, I was using RFUNC & I knew it was rather old... silly me!... I'll
change it & use freeAddhoc UDF. Well, I need at least 4 decimals in quantity
& 6 in prices (for some products). So, I'll change the UDF library, the SQL
scripts & try the CAST function.   
I'll keep you informed.

Thanks once more, André, for your kind help

Jaime Escobal