how to get the thousands separator
can anyone tell me how can i make the thousand separator appers i a regular
select in sql editor or dbaccess
i can't get it in decimal or money field. in the money field i can only
change the money sign before or after and the fraction sign but not the
thosands separator
"oz shimony" <ozs@cbs.gov.il> wrote in message news:bo7f85$e17$1@news2.netvision.net.il...
> can anyone tell me how can i make the thousand separator appers i a regular
> select in sql editor or dbaccess
> i can't get it in decimal or money field. in the money field i can only
> change the money sign before or after and the fraction sign but not the
> thosands separator
DBFORMAT, as far as I know , works with only 4GL. Or may be I am wrong.
For it to work in dbaccess or in SQL, write a stored procedure or a
function. I am attaching a function which I use for our reports
in SPL and Perl. This follows the American way of thousand seperator, which may
differ from the European style. Please change the function
accordingly.
create function format_money(p_money money(16,2),p_showcents integerdefault 1) returning varchar(16);
define w_char varchar(16);
define tmp,tmp2,w_token varchar(16);
define i,j smallint ;
define w_cents char(2);
let w_char = " " ;
if (p_money is NULL ) then
return w_char ;
end if ;
let tmp = p_money ;
let tmp = trim(tmp);
for i = length(tmp) to 1
if ( substr(tmp,i,1) = '.' ) then
exit for ;
end if ;
end for ;
if ( i > 1 ) then
let tmp2 = substr(tmp,1,i-1);
let w_cents = substr(tmp,i+1,length(tmp));
end if ;
let tmp2 = trim(tmp2);
let tmp = substr(tmp2,2,length(tmp2));
let w_token = " " ;
let j = 0 ;
for i = length(tmp) to 1
if (( j = 3 ) and (substr(tmp,i,1) <> '-' )) then
let w_token = ',' || trim(w_token) ;
let j = 0 ;
end if ;
let w_token = substr(tmp,i,1) || trim(w_token) ;
let j = j+1 ;
end for ;
let j = length(w_token);
let w_char = substr(w_token,1,j);
if (p_showcents = 1 ) then
let w_char = w_char || '.' || w_cents ;
end if ;
return (w_char);
end function ;
the function can be called as follows:-
select format_money(amt_total) from systables where tabid = 1 ;
assuming amt_total value is 556789123.34
the output will be 556,789,123.34
The second argument to the function is optional. When it is passed as zero,
the cents is supressed. For some reports, cents may be omitted for better
readability.
select format_money(amt_total,0) from systables where tabid = 1 ;
The output will be 556,789,123
--
email id is bogus