All Forums Tools
murthy1234 4 posts Joined 06/12
03 Oct 2014
Get a Thousand Separators Using BTEQ Export Script
(FORMAT 'G999999999D99' )

Hi
I am using BTEQ script to export the data returned by a query (say like below) to a file called sample.txt
sel
salary
from employee
Now I am getting the sal as 100000.but I want the output in my sample.txt as 100,000with thousand separators).
I tried to use the FORMAT G9999999999D99.#
But my output in the file is coming as 100000.
can any help me how to get 100,000 in the sample.txt(i.e. exported file)
 
Regards
Sri
 
 
 

Raja_KT 1246 posts Joined 07/09
03 Oct 2014

You can try something like this CAST (your_field  AS FORMAT ‘$$$,$$$’)
In TD studio, you can also see Result Viewer prefrences, where you can change the numeric presentation.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

murthy1234 4 posts Joined 06/12
03 Oct 2014
select
cast((CAST(COALESCE(TRIM(a.salary),'                    ') as DECIMAL(19,2)) (FORMAT '$$$,$$$' )) as decimal(19,2)) as salary,
from
employee

Hi Raja
Thanks for the reply.
I tried with you suggestion but still I am not getting any tousand separators.
Attached the code what I am exactly using.
 
Regards#
Sri
 

Raja_KT 1246 posts Joined 07/09
03 Oct 2014

Did you do in unix, bteq or sql assistant or td studio or where?
in bteq,unix:
select cast(100000 as format '$$$,$$$$');
it looks like you want in this format too 
CAST (salary AS FORMAT  ‘$$$,$.............$$9.99’)
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
03 Oct 2014

Hi Sri,
you need to implicitly place the thousand seperators, some countries use hundred seperators :)
Do you need leading zeroes?
format '-999,999,999,999.99'
Otherwise:
format '----,---,---,---,--9.99'

Dieter

david.craig 73 posts Joined 05/13
03 Oct 2014

Hi Sri,
Your format string is correct and should work. See the following submitted from bteq with fieldmode.
BTEQ -- Enter your SQL request or BTEQ command:
sel 100000 (FORMAT 'G999999999D99');
 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.
100000
--------------
000,100,000.00
To trim the leading zeroes, you can use a floating format character like 'L'.
 BTEQ -- Enter your SQL request or BTEQ command:
sel 100000 (FORMAT 'GL(9)D99');
 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.
100000
--------------
   $100,000.00
The response from help session will return the current formatting rules as defined in the SDF. For example:
help session;
 *** Help information returned. One row.
 *** Total elapsed time was 1 second.
...
                 Radix Separator .
                 Group Separator ,
                   Grouping Rule 3
        Currency Radix Separator .
        Currency Group Separator ,
          Currency Grouping Rule 3
                   Currency Name US Dollars
                        Currency $
                     ISOCurrency USD
              Dual Currency Name US Dollars
                   Dual Currency $
                Dual ISOCurrency USD
          Default ByteInt format -(3)9
          Default Integer format -(10)9
         Default SmallInt format -(5)9
          Default Numeric format --(I).9(F)
             Default Real format -9.99999999999999E-999
             Default Time format HH:MI:SS.S(F)Z
        Default Timestamp format YYYY-MM-DDBHH:MI:SS.S(F)Z
.....
It is a best practice to use the i18n formatting characters, instead of locale specific characters like ',', '$', and '.', as the format string does not need to change with the locale. Refer to chapter 12 of the SQL Data types and Literals reference manual for more details.

- Dave

You must sign in to leave a comment.