Query with FORMAT'$$$,$$9.99' Not displaying $ as expected.

Analytics
Enthusiast

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.

2 REPLIES
Junior Supporter

Re: Query with FORMAT'$$$,$$9.99' Not displaying $ as expected.

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.

Senior Apprentice

Re: Query with FORMAT'$$$,$$9.99' Not displaying $ as expected.

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