Get a Thousand Separators Using BTEQ Export Script

Tools & Utilities

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

5 REPLIES
Enthusiast

Re: Get a Thousand Separators Using BTEQ Export Script

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.

Re: Get a Thousand Separators Using BTEQ Export Script

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

Enthusiast

Re: Get a Thousand Separators Using BTEQ Export Script

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’)

Junior Contributor

Re: Get a Thousand Separators Using BTEQ Export Script

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'

Teradata Employee

Re: Get a Thousand Separators Using BTEQ Export Script

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