WIDTH - Basic Teradata Query

Purpose  

Specifies the maximum number of characters for each line in a report.

Syntax  

where:

Syntax Element	Specification
n	The page width in characters. The value for data exported to a file in report format is 254 characters by default. The minimum allowed width is 20 characters; the maximum is 65531 characters.

Usage Notes  

The number specified with the WIDTH command determines the maximum number of characters in a line of the report that is either printed or displayed at the terminal. The value of the WIDTH setting when BTEQ is invoked is 75 characters by default.

The WIDTH command can be used after using the EXPORT command to change the width of the exported file. Upon EXPORT RESET command use, the WIDTH setting is reset to its original value, the value that was in effect before that export.

The WIDTH command can be used in a Teradata SQL macro.

For mainframe-attached systems:

The WIDTH specification is the total count of printable characters; it does not include the carriage control characters.

The WIDTH specification must be equal to or less than the LRECL. The report lines are truncated if the WIDTH specification is greater than the LRECL for the destination file. For Unicode UTF-8 and UTF-16 sessions, LRECL must make available up to 4 bytes per character of the WIDTH.)

Setting a variable RECFM (V, VA, VB, VBA) is recommended whenever a multi-byte session character set is used, since a single character could be multiple bytes.

Carriage control for REPORT and DIF format export files is not supported for Unicode on BTEQ running under z/OS.

DBS Export Width

When using BTEQ to format non-UTF-16 query results containing multi-byte characters, a WIDTH setting (or the LRECL for the SYSPRINT dataset) should be specified to test the DBS Export Width rules. If the WIDTH setting is too small, the bytes for a particular character might get truncated. For example, if a multi-byte string is truncated or wrapped to the next line of a report because a line is too long, the SI character might be lost, or a multi-byte character might be broken. See the International Character Set Support reference for further details on DBS Export Width.

Example  

The following example returns the selected information first with the WIDTH specification set at the screen default, 75 characters, then with it set at 40 and 20 characters:

   database workforce;    

   .defaults     

   .format on     

   .set foldline on all   

   .set sidetitles on     

   .skipline on 4     

   .heading 'Width Off'     

   select   deptname (title 'Department')        

   ,loc (title 'Loc')        

   ,mgrno (title 'Manager No')    

   from     department     

   order by loc    

   ;    

   .defaults     

   .format on    

   .set foldline on    

   .set sidetitles on     

   .heading 'width 40'     

   .width 40     

   = 1    

   .defaults    

   .format on    

   .set foldline on    

   .sidetitles     

   .heading 'width 20'     

   .width 20    

   = 1    

   .defaults     

   .format off    

   .logoff     

   .exit

BTEQ Response

   *** Query completed. 5 rows found. 3 columns returned.

                          Width Off                       

   Department   Engineering      Loc ATL    Manager No 10012

   Department   Manufacturing    Loc CHI    Manager No 10007

   Department   Marketing        Loc  NYC   Manager No 10021

   Department   Administrati0n   Loc NYC    Manager No 10011

   Department   Exec Office      Loc NYC    Manager No 10018

   *** Query completed. 5 rows found. 3 columns returned.

                   width 40                

   Department  Engineering

   Loc  ATL    Manager No   10012

   Department  Manufacturing

   Loc  CHI    Manager No   10007

   Department  Marketing

   Loc  NYC    Manager No   10021

   Department  Administration

   Loc  NYC    Manager No   10011

   Department  Exec Office

   Loc  NYC    Manager No   10018

   *** Query completed. 5 rows found. 3 columns returned.

         width 20      

   Department Engineeri

          Loc ATL    

   Manager No 10012

   Department Manufactu

          Loc CHI    

   Manager No 10007

   Department Marketing

          Loc NYC    

   Manager No 10021

   Department Administr

          Loc NYC    

   Manager No 10011

   Department Exec Offi

          Loc NYC    

   Manager No 10018

If an invalid WIDTH specification is entered, BTEQ displays an error message as shown in the following example:

   .set width 65532      

   *** Error:  Width value must be in the 20 to 65531 range.      

Example 2

The Example 1 WIDTH command in a Teradata SQL macro appears as:

   ECHO '.SET WIDTH 60';