Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 4 of 4 total |
CAST(<MONEYtypeValue>, VARCHAR(10)) forgets its formatting |
Sat, Sep 1 2007 6:29 AM | Permanent Link |
Chris Erdal | 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) |
Sat, Sep 1 2007 8:41 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Sat, Sep 1 2007 12:03 PM | Permanent Link |
Chris Erdal | "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) |
Tue, Sep 4 2007 9:28 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |