TD_GetRowsWithoutMissingValues

May 18

This function is used to return rows that do not contain nulls from a set of target columns. It is a table operator and as such has a number of advantages over conditional SQL statements that perform row by row evaluation.

Single-pass columnar scan — The function is implemented as a compiled C/C++ table operator that scans all columns in a single pass.
Push-down to AMPs — Like other Teradata table operators, it runs natively on each AMP in parallel without requiring a spool step to apply the filter, reducing I/O.
No need to enumerate columns — It handles all columns (or a specified subset) automatically, which also avoids planner overhead from a long WHERE clause with many predicates.
Avoids spool materialisation — A long WHERE col IS NOT NULL AND ... SQL statement can force intermediate spool writes; the table operator pipelines the result directly.

The more target columns you provide as input the greater the gains in efficiency over conditional SQL clauses. For a small number of columns a targeted WHERE IS NOT NULL may actually be comparable.

Syntax

TD_GetRowsWithoutMissingValues (
  ON { table | view | (query) } AS InputTable
  [ PARTITION BY ANY [ ORDER BY order_column ] ]

  [ USING
    TargetColumns ({ 'target_column' | target_column_range }[,...])

    [ Accumulate ({ 'accumulate_column' | accumulate_column_range }[,...]) ]
  ]
)

The function syntax and detail is here, TD_GetRowsWithoutMissingValues • Database Analytic Functions • Reader • Teradata Developers Portal

This example uses a small generated dataset with a banking context to demonstrate the function.

1. Create the Sample Banking Dataset

This section creates a realistic banking table containing customer demographics, account information, and branch details. Several columns intentionally contain NULL values so we can demonstrate how TD_getRowsWithoutMissingValues removes incomplete records.

-- Create sample banking table

CREATE SET TABLE banking_customers (
    customer_id INT,
    approved SMALLINT,
    tier SMALLINT,
    name VARCHAR(50),
    gender VARCHAR(10),
    age INT,
    accounts INT,
    dependents SMALLINT,
    account_id VARCHAR(10),
    balance DECIMAL(15,2),
    branch VARCHAR(10),
    region VARCHAR(20)
);

2. Load Sample Data with Missing Values

The dataset includes missing values in several columns including accounts, branch, and region. These incomplete records simulate the type of data quality issues commonly encountered before analytics or machine learning processing.

-- Insert sample records containing NULL values

INSERT INTO banking_customers
SELECT 1, 1, 1, 'Adams; Mr. James Mitchell', 'male', 45, 2, 1,
       'A001', 125000.00, 'NYC', 'Northeast'
FROM dbc.dbcinfov

UNION ALL

SELECT 2, 0, 2, 'Baker; Mrs. Sarah Elizabeth', 'female', 38,
       CAST(NULL AS INT), 2, 'A002', 45000.00,
       'LAX', CAST(NULL AS VARCHAR(20))
FROM dbc.dbcinfov;

-- Additional rows omitted for brevity

3. Verify the Dataset

Before applying any cleansing logic, verify that the expected number of records has been loaded.

-- Count records

SELECT COUNT(*) AS total_records
FROM banking_customers;

4. Traditional NULL Filtering

A conventional SQL approach requires explicitly checking every column for NULL values. This becomes increasingly difficult to maintain as the number of columns grows.

-- Manually remove rows with NULL values

SELECT *
FROM banking_customers
WHERE accounts IS NOT NULL
  AND age IS NOT NULL
  AND dependents IS NOT NULL;

``

5. Using Multiple Columns

TD_GetRowsWithoutMissingValues simplifies data cleansing by automatically removing rows containing missing values in the specified columns. This approach is easier to read and maintain than a large collection of IS NOT NULL predicates.

-- Remove rows with NULLs in selected columns

SELECT *
FROM TD_getRowsWithoutMissingValues (
    ON banking_customers AS InputTable
    USING
    TargetColumns ('accounts','age','branch','region')
) AS dt;

Result: Only rows where all four target columns contain valid values are returned.

6. Use a Column Range

When multiple adjacent columns require validation, a column range can be used instead of listing every column individually. This is particularly useful on wide tables.

-- Check all columns from accounts through region

SELECT *
FROM TD_getRowsWithoutMissingValues (
    ON banking_customers AS InputTable
    USING
    TargetColumns ('[accounts:region]')
) AS dt;

Result: Any row containing a NULL value in any column between accounts and region is excluded from the output.

7. Key Benefits

TD_getRowsWithoutMissingValues offers several advantages over traditional SQL filtering:

Reduces repetitive IS NOT NULL conditions.
Simplifies maintenance as datasets evolve.
Supports multiple columns with a single parameter.
Supports column ranges for wide datasets.
Produces cleaner input data for downstream analytics and machine learning workflows.

8. Clean Up

Remove the demonstration table after testing.

-- Drop demo table

DROP TABLE banking_customers;

TD_getRowsWithoutMissingValues.sql

James Armitage