NULL - Basic Teradata Query

Basic Teradata Query Reference

Product
Basic Teradata Query
Release Number
16.10
Published
May 2017
Language
English (United States)
Last Update
2018-06-28
dita:mapPath
wmy1488824663431.ditamap
dita:ditavalPath
Audience_PDF_include.ditaval
dita:id
B035-2414
lifecycle
previous
Product Category
Teradata Tools and Utilities

Purpose

Specifies a character or a character string to represent a null field value for Field Mode reports.

Syntax



where the following is true:

string
A quoted string of up to 253 characters.

The initial default value is a single question mark character.

Usage Notes

The character or string specified by a NULL command during a session remains in effect until a new NULL command is used.

Do not use special characters within the string because special characters might be interpreted differently by different output devices. It might be necessary to modify a script that uses special characters if the output is routed to another device.

If the string has an apostrophe (single quote) character, use the second form of the NULL command (the one with quotes as delimiters) or double the apostrophe. For example:

.SET NULL AS "value's missing"

or

.SET NULL AS 'value''s missing'

If the string does not have an apostrophe, the two forms of the NULL command are equivalent.

The NULL setting only affects the output for Field Mode, not Record Mode, Indicator Mode, or Multipart Indicator Mode.

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

Example 1 – NULL

The following two select operations show the results of using the NULL command to specify what character(s) are used to represent returned null field values:

.logon sia1/td1,td1database;
.defaults
.format on
.set null as '0'
select Name,DeptNo,NULLIFZERO(hcap)(Title
'Handicap')from Employee
order by Name;
.set null as '-'
=1;
.format off
.logoff
.exit

BTEQ Response

In response to the first select operation, using zero as the null character, BTEQ returns the following results:

   *** Query completed. 21 rows found. 3 columns returned.
   *** Total elapsed time was 1 second. 
90/11/05 select Name ,DeptNo,NULLIFZERO (hcap)(Title 'Handicap ... Page    1
   Name          DeptNo    Handicap
   ------------  ------    --------
   Aguilar J        600           0
   Brangle B        700           0
   Carter J         500           0
   Chin M           100           0
   Clements D       700           0
   Greene W         100           0
      .
      .
      .
    (etc)

In response to the second select operation, using the hyphen as the null character, BTEQ returns the following results:

   *** Query completed. 21 rows found. 3 columns returned.
   *** Total elapsed time was 1 second. 
   90/11/05 select Name,DeptNo,NULLIFZERO (hcap)(Title 'Handicap ... Page    1
   Name           DeptNo   Handicap
   ------------   ------   --------
   Aguilar J         600          -
   Brangle B         700          -
   Carter J          500          -
   Chin M            100          0
   Clements D        700          0
   Greene W          100          0
      .
      .
      .
    (etc)

In response to the second select operation, using the hyphen as the null character, BTEQ returns the following results:

   *** Query completed. 21 rows found. 3 columns returned.
   *** Total elapsed time was 1 second. 
   90/11/05 select Name,DeptNo,NULLIFZERO (hcap)(Title 'Handicap ... Page    1
   Name           DeptNo   Handicap
   ------------   ------   --------
   Aguilar J         600          -
   Brangle B         700          -
   Carter J          500          -
   Chin M            100          -
   Clements D        700          -
   Greene W          100          -
      .
      .
      .
    (etc)

Example 2 – NULL instigated by a macro

The Example 1 NULL commands in a Teradata SQL macro appear as:

ECHO '.SET NULL AS ''0'' ';
ECHO '.SET NULL AS ''-'' ';

or as:

ECHO '.SET NULL AS "0"';
ECHO '.SET NULL AS "-"';