[firebird-support] Re: Is this a bug in ROUND function?

[pre...@technisoft-online.com [firebird-support]]

Maybe the 'problem' is not in the ROUND() function but the CAST() ?
 

 Peter
 Technisoft
 

Re: [firebird-support] Re: Is this a bug in ROUND function?

[Richard Damon rich...@damon-family.org [firebird-support]]

On 8/29/19 3:14 PM, 'Alan McDonald' a...@meta.com.au [firebird-support]
wrote:
>  
>
> On 8/29/19 4:45 AM, m.djo...@gmail.com [firebird-support] wrote:
> >
> > In binary representation of the values maybe 4.72 is the closest, but
> > we are talking about mathematic.
> > This is from the description of the ROUND function in the documentation:
> > Rounds a number to the nearest integer. If the fractional part is
> > exactly 0.5, rounding is upward for positive numbers and downward for
> > negative numbers.
> > So .725 is all cases is rounded up to .73 as it should be, but in one
> > case it is not.
> >
> The issue is that when you write: cast(2.725 as double precision) you
> aren't dealing with 'mathematics' anymore, or even the value 2.725
>
> The value that you get at that point will be the nearest value to
> 2.725 that is expressible as a double precision floating point number
> which will be slightly different since 2.725 is NOT exactly
> representable as double precision floating point number. The number
> you get is allowed to be either the representable value just below or
> just above, the value, though the preference is the closer one. If the
> number you get is something like 2.72499...xx then round needs to
> round down, if you get something like 2.72500..xx then round needs to
> round up.
>
> If the numbers you had WERE exactly representable, like 2.625 (21/8)
> then the rules on how to round would matter, but since the number you
> have, after being made representable, isn't exactly 0.5 in the
> fractional part, that clause doesn't apply.
>
> --
> Richard Damon
>
> What about:
> select
> round(cast(0.725 as double precision), 2),
> round(cast(1.725 as double precision), 2),
> round(cast(2.725 as double precision), 2),
> round(cast(3.725 as double precision), 2),
> round(cast(4.725 as numeric(18,3)), 2),
> round(cast(5.725 as double precision), 2),
> round(cast(6.725 as double precision), 2),
> round(cast(7.725 as double precision), 2),
> round(cast(8.725 as double precision), 2),
> round(cast(9.725 as double precision), 2),
> round(cast(10.725 as double precision), 2) from
> rdb$database
>
Since numeric is defined by internally scaling up by the power of 10
specified, it should work. (I would probably try to be consistent and
make ALL of them use numeric.)

One question is to make sure that round understands numeric types (I
would think it would).

numeric(18, 3) will use a double for its internal representation, but I
would think it should still work. It might make more sense to use a
width of 9 or less, unless elsewhere you are actually using bigger numbers.

Note that unless you started with numeric, if the value was actually
stored in the database or somehow computed, one danger with this method
is that (assuming casting to numeric rounds) this will cause a double
rounding causing values above x.xx45 to round up.

-- 
Richard Damon

RE: [firebird-support] Re: Is this a bug in ROUND function?

['Alan McDonald' a...@meta.com.au [firebird-support]]

On 8/29/19 4:45 AM, m.djo...@gmail.com [firebird-support] wrote:
>  
> In binary representation of the values maybe 4.72 is the closest, but 
> we are talking about mathematic.
> This is from the description of the ROUND function in the documentation:
> Rounds a number to the nearest integer. If the fractional part is 
> exactly 0.5, rounding is upward for positive numbers and downward for 
> negative numbers.
> So .725 is all cases is rounded up to .73 as it should be, but in one 
> case it is not.
>
The issue is that when you write: cast(2.725 as double precision) you aren't 
dealing with 'mathematics' anymore, or even the value 2.725

The value that you get at that point will be the nearest value to 2.725 that is 
expressible as a double precision floating point number which will be slightly 
different since 2.725 is NOT exactly representable as double precision floating 
point number. The number you get is allowed to be either the representable 
value just below or just above, the value, though the preference is the closer 
one. If the number you get is something like 2.72499...xx then round needs to 
round down, if you get something like 2.72500..xx then round needs to round up.

If the numbers you had WERE exactly representable, like 2.625 (21/8) then the 
rules on how to round would matter, but since the number you have, after being 
made representable, isn't exactly 0.5 in the fractional part, that clause 
doesn't apply.


--
Richard Damon

What about:
select
 round(cast(0.725 as double precision), 2),
 round(cast(1.725 as double precision), 2),
 round(cast(2.725 as double precision), 2),
 round(cast(3.725 as double precision), 2),
 round(cast(4.725 as numeric(18,3)), 2),
 round(cast(5.725 as double precision), 2),
 round(cast(6.725 as double precision), 2),
 round(cast(7.725 as double precision), 2),
 round(cast(8.725 as double precision), 2),
 round(cast(9.725 as double precision), 2),
 round(cast(10.725 as double precision), 2) from
 rdb$database

Re: [firebird-support] Re: Is this a bug in ROUND function?

[Richard Damon rich...@damon-family.org [firebird-support]]

On 8/29/19 4:45 AM, m.djo...@gmail.com [firebird-support] wrote:
>  
> In binary representation of the values maybe 4.72 is the closest, but
> we are talking about mathematic.
> This is from the description of the ROUND function in the documentation:
> Rounds a number to the nearest integer. If the fractional part is
> exactly 0.5, rounding is upward for positive numbers and downward for
> negative numbers.
> So .725 is all cases is rounded up to .73 as it should be, but in one
> case it is not.
>
The issue is that when you write: cast(2.725 as double precision) you
aren't dealing with 'mathematics' anymore, or even the value 2.725

The value that you get at that point will be the nearest value to 2.725
that is expressible as a double precision floating point number which
will be slightly different since 2.725 is NOT exactly representable as
double precision floating point number. The number you get is allowed to
be either the representable value just below or just above, the value,
though the preference is the closer one. If the number you get is
something like 2.72499...xx then round needs to round down, if you get
something like 2.72500..xx then round needs to round up.

If the numbers you had WERE exactly representable, like 2.625 (21/8)
then the rules on how to round would matter, but since the number you
have, after being made representable, isn't exactly 0.5 in the
fractional part, that clause doesn't apply.


-- 
Richard Damon

ODP: [firebird-support] Converting with parameters stored in variables?

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Hi

This is called padding.
Simple example (but not what you want you must padd also decimal point)
SELECT LPAD(CAST(100 AS NUMERIC(10,3)), 14, '0') FROM RDB$DATABASE

But you must tell us what is your real problem, as you need padding for what?

Pozdrawiam,
Karol Bieniaszewski

Re: [firebird-support] Converting with parameters stored in variables?

[Virna Constantin costel...@yahoo.com [firebird-support]]

 select right('0'||(10.3+0.000),10) from rdb$database



Hello,

  

I get from a application the following

  

100

R10.3

Or 

22.22

R10.3

  

Now I should take it into:

  

000100.000

And

22.220

  

I think, I cannot simply (cast :input as :vartype) (vartype = ‘decimal(10.3)’

Also it will fortunately not work: cast(input as decimal(:v1,:v2) (v1 = 10 v2 = 
3).

  

How can I realize it without dismantle the 10.3 and the 22.22?

  

Thank you.

  
  

[firebird-support] Converting with parameters stored in variables?

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello,

 

I get from a application the following

 

100

R10.3

Or 

22.22

R10.3

 

Now I should take it into:

 

000100.000

And

22.220

 

I think, I cannot simply (cast :input as :vartype) (vartype =
'decimal(10.3)'

Also it will fortunately not work: cast(input as decimal(:v1,:v2) (v1 = 10
v2 = 3).

 

How can I realize it without dismantle the 10.3 and the 22.22?

 

Thank you.

 

 

Re: [firebird-support] Re: Is this a bug in ROUND function?

[Slavomir Skopalik skopa...@elektlabs.cz [firebird-support]]

For exact numbers you have to use exact numeric types.

Double is float point type with all advantages and disadvantages.

Look here 
https://en.wikipedia.org/wiki/Double-precision_floating-point_format


Just for fun, try this:

1e20 + 1 + 1 - 1e20 + 1

Slavek

Ing. Slavomir Skopalik
Executive Head
Elekt Labs s.r.o.
MASA - Collection and evaluation of data from machines and laboratories
http://eng.elektlabs.cz/products-and-services/masa
-
Address:
Elekt Labs s.r.o.
Chaloupky 158
783 72 Velky Tynec
Czech Republic
---
Mobile: +420 724 207 851
icq:199 118 333
skype:skopaliks
e-mail:skopa...@elektlabs.cz
http://www.elektlabs.cz

On 29.08.19 13:45, m.djo...@gmail.com [firebird-support] wrote:
In binary representation of the values maybe 4.72 is the closest, but 
we are talking about mathematic.

This is from the description of the ROUND function in the documentation:
Rounds a number to the nearest integer. If the fractional part is 
exactly 0.5, rounding is upward for positive numbers and downward for 
negative numbers.
So .725 is all cases is rounded up to .73 as it should be, but in one 
case it is not.



Posted by: m.djo...@gmail.com

Reply via web post 
 
	• 	Reply to sender 
 
	• 	Reply to group 
 
	• 	Start a New Topic 
 
	• 	Messages in this topic 
 
(3)


++

[firebird-support] Re: Is this a bug in ROUND function?

[m.djo...@gmail.com [firebird-support]]

In binary representation of the values maybe 4.72 is the closest, but we are 
talking about mathematic. 

 This is from the description of the ROUND function in the documentation:
 Rounds a number to the nearest integer. If the fractional part is exactly 0.5, 
rounding is upward for positive numbers and downward for negative numbers.
 So .725 is all cases is rounded up to .73 as it should be, but in one case it 
is not.

Re: [firebird-support] MS-Windows<-->Linux compatibility

[emou...@yahoo.fr [firebird-support]]

>
> In common case - yes.
 > But you cannot "use database in application", 
> you only can use Firebird server that 
 > work with database.
>

Thank you for the answer.

And I agree for the clarification, which I interpret as specifying that the 
Firebird server under MS-Windows which first allows me to create and open the 
database file under MS-Windows, must be of a version lower or equal to that of 
the Firebird server under Linux which will then open this same *.fdb file 
(excluding MS-Windows or Linux rights, applied on the *.fdb file itself).

Is that "definitely" correct?

Re: [firebird-support] MS-Windows<-->Linux compatibility

[Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]]

29.08.2019 13:06, emou...@yahoo.fr [firebird-support] wrote:
> In theory, if I create a Firebird database with IBExpert under MS-Windows, is 
> it then 
> possible to use it with a Linux application (written in Lazarus), giving it 
> all the right 
> rights, ie without going through an IBExpert's script export and re-import 
> into FlameRobin)?

   In common case - yes.
   But you cannot "use database in application", you only can use Firebird 
server that 
work with database.


-- 
   WBR, SD.






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

[firebird-support] MS-Windows<-->Linux compatibility

[emou...@yahoo.fr [firebird-support]]

Hello,

In theory, if I create a Firebird database with IBExpert under MS-Windows, is 
it then possible to use it with a Linux application (written in Lazarus), 
giving it all the right rights, ie without going through an IBExpert's script 
export and re-import into FlameRobin)? 

Sincerely,
 Eric.

RE: [firebird-support] Array datatype?

['Alan McDonald' a...@meta.com.au [firebird-support]]

Hello Kjell,

 

then I have to do the mapping between Monday = 1, Tuesday = 2, Sunday = 0. But 
I can only select the value with an execute statement.

 

I will test it, thank you. 

 

WHEN F_DAYOFWEEK(J.COMMDATE)=1 /* Sunday */

??

 

Alan McDonald

 

 

 

AW: [firebird-support] Array datatype?

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello Kjell,

 

then I have to do the mapping between Monday = 1, Tuesday = 2, Sunday = 0. But 
I can only select the value with an execute statement.

 

I will test it, thank you. 

 

Von: firebird-support@yahoogroups.com  
Gesendet: Donnerstag, 29. August 2019 07:46
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] Array datatype?

 

  

Den 2019-08-29 kl. 07:17, skrev 'Check_Mail' check_m...@satron.de 
  
[firebird-support]: 
> 
> Okay, 
> 
> unfortunately there is no coherence between the weekday 0-6 and my 
> table-Field Montag, Dienstag etc. (developed historically) and the execute 
> statement works not too (not with "variable = variable + variable" and not 
> with "select variable + variableb from rdb$database" 
> 
> Thank you, Dimitry! 
> 
You should probably consider a lookup table, i.e. whatever you would 
like to put in an array, put it in a separate table instead, one record 
for each array entry. 

If the "array" is different for each execution of the procedure, 
consider using a global temporary table, GTT. You can find more on this 
page (about 1/4 down): 
https://firebirdsql.org/rlsnotesh/rnfb210-ddl.html 

Regards, 
Kjell 


[Non-text portions of this message have been removed]