Money Formating

Posted in 2010

Wyza, Jonathon — Thu, 14 Oct 2010 19:43:49 — source: IIUG Forums & Mailing Lists

Is there a simple way to format a money column in SQL? (eg, I want it to come out as $###,###.##) I know concatenating an empty string to a money field puts in th $ but not the commas. I saw the DBMONEY environment variable, but didn't understand the syntax, so that might just be the answer. Jonathon Wyza CX & CBORD System Administrator CX Programmer/Analyst Administrative Computing Bethel College (574)-257-3381 AIM: Iamwyza jonathon.wyza@bethelcollege.edu<mailto:jonathon.wyza@bethelcollege.edu> ============================== SLES 11x64 & IDS 11.50.FC6 "Don't document the problem, fix it." - Atli Björgvin Oddsson

↪ replying to Wyza, Jonathon

Alexandre Marini — Thu, 14 Oct 2010 20:06:39 — source: IIUG Forums & Mailing Lists

Hello. According to the docs page: http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.glsug.doc/id s_gug_060.htm?resultof=%22%64%62%6d%6f%6e%65%79%22%20 If DBMONEY is not set, and no locale is specified, the currency symbol is the dollar sign, the thousands separator is the comma, and the decimal separator is the period. Isn´t that what you wanted? Best regards! Em 14/10/2010 15:43, Wyza, Jonathon escreveu: > Is there a simple way to format a money column in SQL? (eg, I want it to come > out as $###,###.##) I know concatenating an empty string to a money field puts > in th $ but not the commas. I saw the DBMONEY environment variable, but didn't > understand the syntax, so that might just be the answer. > > Jonathon Wyza > CX& CBORD System Administrator > CX Programmer/Analyst > Administrative Computing > Bethel College > (574)-257-3381 > AIM: Iamwyza > jonathon.wyza@bethelcollege.edu<mailto:jonathon.wyza@bethelcollege.edu> > ============================== > SLES 11x64& IDS 11.50.FC6 > > "Don't document the problem, fix it." > - Atli Björgvin Oddsson > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Alexandre Marini Tecnologia da Informação - DBA msn: alexandre_marini@hotmail.com SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix Cert-Info-Mgmt_color IBM Informix Dynamic Server Certified Professional V10 / V11

↪ replying to Alexandre Marini

Wyza, Jonathon — Thu, 14 Oct 2010 20:08:33 — source: IIUG Forums & Mailing Lists

Sadly, the OLE driver doesn't work like that. If nothing is set then it is $####.00 (no comma is set). Jonathon Wyza CX & CBORD System Administrator CX Programmer/Analyst Administrative Computing Bethel College (574)-257-3381 AIM: Iamwyza jonathon.wyza@bethelcollege.edu ============================== SLES 11x64 & IDS 11.50.FC6 "Don't document the problem, fix it." - Atli Björgvin Oddsson -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Alexandre Marini Sent: Thursday, October 14, 2010 4:07 PM To: ids@iiug.org Subject: Re: Money Formating [21698] Hello. According to the docs page: http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.glsug.doc/id s_gug_060.htm?resultof=%22%64%62%6d%6f%6e%65%79%22%20 If DBMONEY is not set, and no locale is specified, the currency symbol is the dollar sign, the thousands separator is the comma, and the decimal separator is the period. Isn´t that what you wanted? Best regards! Em 14/10/2010 15:43, Wyza, Jonathon escreveu: > Is there a simple way to format a money column in SQL? (eg, I want it > to come > out as $###,###.##) I know concatenating an empty string to a money > field puts > in th $ but not the commas. I saw the DBMONEY environment variable, > but didn't > understand the syntax, so that might just be the answer. > > Jonathon Wyza > CX& CBORD System Administrator > CX Programmer/Analyst > Administrative Computing > Bethel College > (574)-257-3381 > AIM: Iamwyza > jonathon.wyza@bethelcollege.edu<mailto:jonathon.wyza@bethelcollege.edu > > > ============================== > SLES 11x64& IDS 11.50.FC6 > > "Don't document the problem, fix it." > - Atli Björgvin Oddsson > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Alexandre Marini Tecnologia da Informação - DBA msn: alexandre_marini@hotmail.com SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix Cert-Info-Mgmt_color IBM Informix Dynamic Server Certified Professional V10 / V11 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Wyza, Jonathon

Art Kagel — Thu, 14 Oct 2010 20:45:22 — source: IIUG Forums & Mailing Lists

DBMONEY specifies the leading currency symbol if any (so "$" in the US) the decimal separator (so "." in the US and the UK and "," almost everywhere else), and the trailing currency symbol if any (so the Euro symbol, etc.). You can't use it to format a number using commas between every 3rd digit. That would have to be done in the application or there is a function you can call, TO_CHAR() which accepts an optional format mask similar to the ones used in 4GL. The currency and decimal points in the mask are translated according to the DBMONEY setting. So: > select to_char( one, "$$$$,$$$,$$$,$$$.$$") from currency; (expression) $1,234,567,890.12 (expression) $1.20 > select to_char( one, "$###,###,###,###.##") from currency; (expression) $ 1,234,567,890.12 (expression) $ 1.20 See the description of the function in the Guide to SQL Syntax manual. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Oct 14, 2010 at 3:43 PM, Wyza, Jonathon <wyzaj@bethelcollege.edu>wrote: > Is there a simple way to format a money column in SQL? (eg, I want it to > come > out as $###,###.##) I know concatenating an empty string to a money field > puts > in th $ but not the commas. I saw the DBMONEY environment variable, but > didn't > understand the syntax, so that might just be the answer. > > Jonathon Wyza > CX & CBORD System Administrator > CX Programmer/Analyst > Administrative Computing > Bethel College > (574)-257-3381 > AIM: Iamwyza > jonathon.wyza@bethelcollege.edu<mailto:jonathon.wyza@bethelcollege.edu> > ============================== > SLES 11x64 & IDS 11.50.FC6 > > "Don't document the problem, fix it." > - Atli Björgvin Oddsson > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636163d3daece95049299c9a8

↪ replying to Alexandre Marini

Art Kagel — Thu, 14 Oct 2010 20:48:18 — source: IIUG Forums & Mailing Lists

That's true, however, dbaccess by default does not display the commas and applications that retrieve a money column as a string (colname::char(16)) will also not display any commas. You have to use the TO_CHAR() function or some other means of formatting the output string. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Oct 14, 2010 at 4:06 PM, Alexandre Marini <amarini@fazenda.ms.gov.br > wrote: > Hello. > According to the docs page: > > > http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.glsug.doc/id s_gug_060.htm?resultof=%22%64%62%6d%6f%6e%65%79%22%20 > > If DBMONEY is not set, and no locale is specified, the currency symbol > is the dollar sign, the thousands separator is the comma, and the > decimal separator is the period. > > Isn´t that what you wanted? > > Best regards! > > Em 14/10/2010 15:43, Wyza, Jonathon escreveu: > > Is there a simple way to format a money column in SQL? (eg, I want it to > come > > out as $###,###.##) I know concatenating an empty string to a money field > puts > > in th $ but not the commas. I saw the DBMONEY environment variable, but > didn't > > understand the syntax, so that might just be the answer. > > > > Jonathon Wyza > > CX& CBORD System Administrator > > CX Programmer/Analyst > > Administrative Computing > > Bethel College > > (574)-257-3381 > > AIM: Iamwyza > > jonathon.wyza@bethelcollege.edu<mailto:jonathon.wyza@bethelcollege.edu> > > ============================== > > SLES 11x64& IDS 11.50.FC6 > > > > "Don't document the problem, fix it." > > - Atli Björgvin Oddsson > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > > Alexandre Marini > > Tecnologia da Informação - DBA > > msn: alexandre_marini@hotmail.com > > SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix > > Cert-Info-Mgmt_color > > IBM Informix Dynamic Server Certified Professional V10 / V11 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016362837ec3059e1049299d4df

Related threads

Glossary

IDS (Informix Dynamic Server)