DEPARTMENT_NAME
is always empty when EMPLOYEE_ID_OF_SUPERVISOR
is empty and vice versa. This indicates that there might be a common reason. EMPLOYEE_ID
DEPARTMENT_ID
DEPARTMENT_NAME
EMPLOYEE_ID_OF_SUPERVISOR
NULL
NULL
NULL
NULL
DEPARTMENT_ID
is missing from the departments table. Thus, all the fields that you select from the departments table, i.e. DEPARTMENT_NAME
or EMPLOYEE_ID_OF_SUPERVISOR
will be empty as well.GROUP BY
using all indicator columns, you will get a distinct list of all missing value patterns along with the number of observations in each pattern. You can sort them by the number of observations in descending order to get an idea of the most frequent missing value patterns.EMPLOYEE_ID
DEPARTMENT_ID
DEPARTMENT_NAME
EMPLOYEE_ID_OF_SUPERVISOR
N
K
is the number of columns in the table, you will end up with K
indicator columns and one count column. Writing the SQL that calculates the missing value patterns will become tedious if the number of columns K
is large. Of course, you can use SQL to generate the SQL from the table metadata, using for example the table DBC.COLUMNSV
in Teradata that contains a list of columns of each table in the database.K
characters. That way, the intermediate result is typically smaller and you only need to group by a single column.