Hi,

I was just wondering if it's possible to Casting a BCD or Float to
String with fixed decimal places in SQL.

In Pascal it would be something similar to

result := floattostrf(MyTableTPrice.value, fffixed, 15, 2);

Cheers

Adam.

Adam


I'm sure someone out there will know a better way but I'd do one of two things

1: write a UDF
2: use CAST, POS & SUBSTR eg

SUBSTR(CAST(float AS CHAR(15)),1,POS('.',CAST(float AS CHAR(15))))+SUBSTR(CAST(float AS CHAR(15)),POS('.',CAST(float AS CHAR(15))),4)

Probably the wrong number of brackets and the decimal point may be missing - I always have to experiment with things like this


Roy Lambert

Adam


I've been playing with it and this

TRIM(BOTH ' 'FROM SUBSTR(CAST(_Fee AS VARCHAR(15)),1,POS('.',CAST(_Fee AS VARCHAR(15)))-1))
+TRIM(BOTH ' ' FROM SUBSTR(CAST(_Fee AS VARCHAR(15)),POS('.',CAST(_Fee AS VARCHAR(15))),4)) AS Formatted,

works (its left justified) as long as there are decimals. If not it looks like an integer

This seems to work

IF(
0 = POS('.',CAST(_Fee AS VARCHAR(10)))
THEN CAST(_Fee AS VARCHAR(7))+'.00'
ELSE SUBSTR(CAST(_Fee AS VARCHAR(10)),1,POS('.',CAST(_Fee AS VARCHAR(10)))+2)
)


you just hasve to adjust the 10 to whatever size field you want and the 2 to the number of decimals. Works for me in ElevateDB, not tested in DBISAM so let me know.

Roy Lambert [Team Elevate]

Adam,

<< I was just wondering if it's possible to Casting a BCD or Float to String
with fixed decimal places in SQL. >>

Try this (assuming that Test is a BCD field with a defined 4 decimal
places):

CAST(CAST(Test AS DECIMAL(19,2)) AS VARCHAR(20))

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim and Roy,

Thanks for your replies...

Unfortunately neither of those seem to work in DBISam 4. Roy's approach
works for some values, but not others oddly enough and Tim's doesn't
seem to work at all.

Either way - it looks like it's not straight forward, so I'll look for
another approach.

Thanks for your time and help guys!

Adam

"Adam H." <ahairsub5@removeme.jvxp.com> wrote in message
news:31BAB6A3-8D38-4011-B9DE-19A157B583FD@news.elevatesoft.com...
> Hi Tim and Roy,
>
> Thanks for your replies...
>
> Unfortunately neither of those seem to work in DBISam 4. Roy's approach
> works for some values, but not others oddly enough and Tim's doesn't seem
> to work at all.
>

You need to catch the values for 1 decimal as in 12.1

select
IF(
0 = POS('.',CAST(Famount AS VARCHAR(10)))
THEN CAST(Famount AS VARCHAR(7))+'.00'
ELSE
IF (POS('.',CAST(Famount AS VARCHAR(10))) +  1 = LENGTH(CAST(FAMOUNT AS
VARCHAR(10))) THEN
CAST (FAMOUNT AS VARCHAR(9)) + '0' ELSE CAST(FAMOUNT AS VARCHAR(10))
))
from TABLE

Robert

Adam


<giggle>

I was going to say that Tim's got ElevateDB on the brain, but testing it doesn't even work in ElevateDB
</giggle>

I think Robert has spotted the problem, but if you are using a recent version of DBISAM I'd go for a UDF - more comprehensible.

Roy Lambert [Team Elevate]

Adam,

<< Unfortunately neither of those seem to work in DBISam 4. >>

That's what I tested it with, but I think that the problem is that I tested
it with numbers containing 4 decimal places - are you using numbers with
*less* than 2 decimals ?  If so, then just pad out the numeric string with
zeros.

--
Tim Young
Elevate Software
www.elevatesoft.com

Adam

> I was just wondering if it's possible to Casting a BCD or Float to
> String with fixed decimal places in SQL.
>
> In Pascal it would be something similar to
>
> result := floattostrf(MyTableTPrice.value, fffixed, 15, 2);

I add a value and then trim the result.  To change the number of decimal
places just change the value being added.

eg 2 decimal places
SELECT LEFT(CAST(MyField+0.001 AS CHAR(20)),LENGTH(CAST(MyField+0.001 as
CHAR(20)))-1) FROM MyTable

4 decimal places
SELECT LEFT(CAST(MyField+0.00001 AS CHAR(20)),LENGTH(CAST(MyField+0.00001 as
CHAR(20)))-1) FROM MyTable

John