New to BTEQ and Teradat Question on Export Report

Tools

New to BTEQ and Teradat Question on Export Report

I am running the following report script:

.LOGON xx.x.x.xx/xxxx,xxxx

.SET Titledashes OFF                        ;

.SET Quiet OFF                              ;

.SET Retry OFF                              ;

.SET Width 5000                             ;

.EXPORT REPORT FILE = yy.txt                ;

.SET format OFF                             ;

.SET recordmode OFF                         ;

.SET sidetitles OFF                         ;

SELECT   F3a || F1a || F2a || F4a (TITLE '')

       , TRIM(  d1 ) (TITLE '')

       , TRIM(  d2 ) (TITLE '')

       , TRIM(  d3 ) (TITLE '')

       , TRIM(  d4 ) (TITLE '')

       , d5 (FORMAT 'ZZZZ9.99')(TITLE '')

       , TRIM(  d6 ) (TITLE '')

       , TRIM(  d7 ) (TITLE '')

d1-d7 should all be numeric

I am trying to understand why    TRIM(  d1 ) (TITLE '') works and why    d1  (TITLE '') does not work?

Also the entire clause after d5 does not work

Finally, do I really have to "roll my own" and manually add \t (tabs) and concatenate to get tab delimited?

4 REPLIES

Re: New to BTEQ and Teradat Question on Export Report

Hello - I think the statement d1-d7 should all be numeric is the problem.

I am creating variables d1-d7 in a subselect using sum - did I have to explicitly assign a data type?

 SELECT  TRIM(  d1 ) (TITLE '')

       , TRIM(  d2 ) (TITLE '')

       , TRIM(  d3 ) (TITLE '')

       , TRIM(  d4 ) (TITLE '')

       , d5 (FORMAT 'ZZZZ9.99')(TITLE '')

       , TRIM(  d6 ) (TITLE '')

       , TRIM(  d7 ) (TITLE '')

         FROM ( SELECT  SUM(SDS_GROSS_UNITS)                                     AS d1

                         , SUM(SDS_RETURN_UNITS)                                    AS d2

                         , SUM(SDS_GROSS_PROMO_UNITS)                               AS d3

                         , SUM(SDS_RETURN_PROMO_UNITS)                              AS d4

                         , SUM(SDS_GROSS_DOLLARS)                                   AS d5

                         , SUM(SDS_RETURN_DOLLARS)                                  AS d6

                         , SUM(SDS_GROSS_PROMO_ALLOW)                               AS d7

                        FROM  ...

                     WHERE     ...

                GROUP BY   ...

                ) AS Original_Query

Teradata Employee

Re: New to BTEQ and Teradat Question on Export Report

The default FORMAT (and default data type) will depend on what you are summing. In FIELD mode, the conversion is done as it is for old-style Teradata type conversions - the length of the resulting character string depends on the FORMAT applied and includes pad characters. But if you do explicit ANSI CAST to VARCHAR, the pad characters will be trimmed. Using TRIM function will do implicit conversion first and then remove the pad characters from the result, so same net effect.

Try .SET SEPARATOR '<tab>'

(where you actually type a tab character in between the quotes)

For d5, try CAST(CAST(d5 AS FORMAT 'ZZZZ9.99') AS VARCHAR(8))

Re: New to BTEQ and Teradat Question on Export Report

Hmmm  I will do that but I really should not have been trimming in the first place so I do not understand why when  d1-d4 are integers and d5 is decimal(15,2) d1-d5 still print titles and d5 does not format in the example below 

 SELECT  d1 (TITLE '')

       , d2 (TITLE '')

       , d3 (TITLE '')

       , d4 (TITLE '')

       , d5 (FORMAT 'ZZZZ9.99')(TITLE '')

        FROM ( SELECT  SUM(SDS_GROSS_UNITS)                                     AS d1

                         , SUM(SDS_RETURN_UNITS)                                    AS d2

                         , SUM(SDS_GROSS_PROMO_UNITS)                               AS d3

                         , SUM(SDS_RETURN_PROMO_UNITS)                              AS d4

                         , SUM(SDS_GROSS_DOLLARS)                                   AS d5

                         , SUM(SDS_RETURN_DOLLARS)                                  AS d6

                         , SUM(SDS_GROSS_PROMO_ALLOW)                               AS d7

                        FROM  ...

                     WHERE     ...

                GROUP BY   ...

                ) AS Original_Query

Teradata Employee

Re: New to BTEQ and Teradat Question on Export Report

Put the .EXPORT after all the .SET commands. And I should have said .SET SEPARATOR '<tab>' ALL (because I doubt you want a line with only blank titles and separator characters).

As far as d5 not being formatted:  Can you give an example of a value, what output you you receive now, and what you want? In general a format is applied only for conversion to/from character data; but field mode implicitly does conversion to character, so the format should be applied (unless the value doesn't fit; in which case you will get all * characters in the field).

You could also consider using Teradata Parallel Transporter, combining the SQL Selector operator and the Data Connector consumer operator, to create a delimited text output file.