I'm trying to concatenate a few fields to give a LongText field for
display purposes, but I can't fathom concatenating a BCD field cast as
MONEY with the rest.

e.g.
SELECT 'N°'+RIGHT('      '+CAST(artNum,VARCHAR(6)),6) ItemNumber,
      CAST(artRetailValueIncTax,MONEY) TextPrice               
FROM Articles A

gives TextPrice as "1 953,20 €" (French style)

whereas

SELECT 'N°'+RIGHT('      '+CAST(artNum,VARCHAR(6)),6) ItemNumber,
      CAST(CAST(artRetailValueIncTax,MONEY),VARCHAR(6)) TextPrice               
FROM Articles A

resets them to "1953.2".

I tried

SELECT 'N°'+RIGHT('      '+CAST(artNum,VARCHAR(6)),6) +' '+
CAST(artRetailValueIncTax,MONEY) TextPrice               
FROM Articles A

in the hope that the CAST would be seen as a CHAR result, given that it
includes the thousands separator and the currency symbol, but got a
complaint that a string-type field or value was expected.

Is there a built-in way to get around this?
--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 4 + EDB 1.04 build 3)

Chris,

<< I'm trying to concatenate a few fields to give a LongText field for
display purposes, but I can't fathom concatenating a BCD field cast as MONEY
with the rest.

e.g.
SELECT 'N°'+RIGHT('      '+CAST(artNum,VARCHAR(6)),6) ItemNumber,
      CAST(artRetailValueIncTax,MONEY) TextPrice
FROM Articles A

gives TextPrice as "1 953,20 €" (French style) >>

Yes, but this is not due to the SQL but rather due to the formatting of the
field types performed by the TDataSet/TField components in Delphi.

<< Is there a built-in way to get around this? >>

Not in SQL - DBISAM's SQL always treats the MONEY type just like a float for
purposes of formatting in a CAST to a string.  I would select the columns
separately and define a calculated column to perform the formatting as
required.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in
news:8B104FAF-CE13-4767-9590-B5EAC9C25D0F@news.elevatesoft.com:

><< Is there a built-in way to get around this? >>
>
> Not in SQL - DBISAM's SQL always treats the MONEY type just like a
> float for purposes of formatting in a CAST to a string.  I would
> select the columns separately and define a calculated column to
> perform the formatting as required.

Thanks. I thought as much.

I was trying to avoid doing it in the OnCalcFields event handler to try
to optimise the time spent on it.

I've got around the problem for now, as long as I'm only selling to EURO
countries that use a comma for the decimal separator with:

SELECT  'N° '+CAST(artNum,CHAR(6))+': '+atyName+' '+sizName+'
'+manName+' '+
       IF(POS(',',CAST(artRetailValueIncTax,CHAR(10)))=0,
              CAST(artRetailValueIncTax,CHAR(10))+',00',
              CAST(artRetailValueIncTax,CHAR(10))+
              REPEAT('0',POS(',',CAST(artRetailValueIncTax,CHAR(10)))+
              2-LENGTH(CAST(artRetailValueIncTax,CHAR(10)))))+ ' €'
LongText

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 4 + EDB 1.04 build 3)

Chris,

<< I've got around the problem for now, as long as I'm only selling to EURO
countries that use a comma for the decimal separator with: >>

DBISAM's SQL always outputs a period (.) as the decimal separator when
casting numeric values to character values.

--
Tim Young
Elevate Software
www.elevatesoft.com