All Forums Analytics
gmoney 23 posts Joined 12/10
26 Apr 2012
Query with FORMAT'$$$,$$9.99' Not displaying $ as expected.

I have a query that runs fine except in my results I am not getting a $ in my currency column.

Here is the line I am using:

sum(CHRGE_AMT)(FORMAT'$$$,$$9.99') MRC,

my results are still simply 136.25 not $136.25 as expected.

Any thoughts on what might be preventing it from displaying?

Trivial Yes, but I am sure you have had that boss at one time or another as well.

 

Release is 12.00.02.44 Version 12.00.02.40  

 

Thanks for looking.

CarlosAL 512 posts Joined 04/08
27 Apr 2012

Hi:

 BTEQ -- Enter your SQL request or BTEQ command:
select 1 (format 'G$$$$9D99');

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.

1
---------
    $1.00

 BTEQ -- Enter your SQL request or BTEQ command:
select 1000 (format 'G$$$$9D99');

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.

1000
---------
$1,000.00
 

HTH.

Cheers.

Carlos.

dnoeth 4628 posts Joined 11/04
27 Apr 2012

Hi Greg,

i assume you submit your query using SQL Assistant. The format was ment to be used in reports run in BTEQ, which requests all data converted to strings.

Outside of BTEQ you need to add a typecast to a [var]char:

sum(CHRGE_AMT)(FORMAT'$$$,$$9.99') (varchar(10))

Dieter

Dieter

You must sign in to leave a comment.