TD_GetRowsWithoutMissingValues
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.
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.
Example script: TD_getRowsWithoutMissingValues.sql
