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 "-"';