NULL - Basic Teradata Query - 16.20

Basic Teradata Query Reference

prodname
Basic Teradata Query
vrm_release
16.20
category
Programming Reference
featnum
B035-2414-108K

Purpose

This control enables users to specify a character string, rather that the default "?", 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 "-"';