FB-3,09 Case & Cast

[Henry Olive 5]

Good Day,

 

Case When (X.A= X1.A) then

     CAST((10.00000 / 0.3048) * 14.7776 as NUMERIC (18,2))

 

I'm getting Numeric Value is out of range error msg.

What is wrong ?

Thank You

 

[Vandrovnik 214]

select
 cast((10.00000 / 0.3048) * 14.7776 as NUMERIC (18,2))
 from rdb$database a

 

It works for me.

Without the cast, there is 13 decimal places, so only 5 places is left in front of the decimal point.

 

[Henry Olive 5]

Thank you Vandrovnik

select
 cast((10.00000 / 0.3048) * 14.7776 as NUMERIC (18,2))
 from rdb$database 

also works in FlameRobin in my side

but in my real sql ( also In FlameRobin )

i'm getting Numeric Value is out of range error msg.

[Vandrovnik 214]

So there must be something different in your real SQL 🙂

 

What about:

select
 cast(cast(10 as double precision) / 0.3048 * 14.7776 as NUMERIC (18,2))
 from rdb$database a

Edited April 8, 2022 by Vandrovnik

[Henry Olive 5]

Thank you Vandrovnik

your codes gives error (token unknown line 1 column 2 cast )

Edited April 8, 2022 by Henry Olive

[Vandrovnik 214]

7 minutes ago, Henry Olive said:

Thank you Vandrovnik

your codes gives error (token unknown line 1 column 2 cast )

I guess you have copied it without the SELECT...

[Henry Olive 5]

You are right, i didnt notice that i forgot to copy Select,

i'm so sorry,  Please accept my apology

Your new code works

Thank You SO MUCH

[Serge_G 87]

Hi

2 hours ago, Henry Olive said:

CAST((10.00000 / 0.3048) * 14.7776 as NUMERIC (18,2))

Some useless .00000 here !

You have to learn about how Firebird do the job with scales don't remember where I read this in doc, but you can see here some instructions

So if you use the SQL you have, before casting, a 13 scaled variable !

SELECT  CAST(10 / 0.3048* 14.7776 AS NUMERIC(13,2)) FROM RDB$DATABASE

This work also

Edited April 8, 2022 by Serge_G

[Henry Olive 5]

Thank you SO MUCH Serge

10.00000 was just a sample, the value could be 2000.05586 or 36500.153596 i dont know

but the value will have 5 decimal because the field is NUMERIC(18,5) in database