Check list of values is NULL in PL/SQL
java, plsql, sql
Solution
Maybe `COALESCE` will help you:
It evaluates a list of values left to right and returns the first value that is `NOT NULL`. If all values are `NULL`, it evaluates to `NULL`, which is a single value that you can check for `IS NULL`.
SELECT * FROM myTable t WHERE t.dataId IN (:myList) OR COALESCE(:myList) IS NULL
Problem
Is there a way to check is a list of values is `NULL` in PL/SQL? I have something along the lines of: ``` SELECT * FROM myTable t WHERE t.dataId IN (:myList) OR :myList IS NULL ``` At run-time, the `:myList` symbol is substituted for a list of strings e.g. ``` SELECT * FROM myTable t WHERE t.dataId IN ('a', 'b', 'c') OR ('a', 'b', 'c') IS NULL ``` I've realised that `('a', 'b', 'c') IS NULL` is invalid PL/SQL, so I wondered if there is another way to check a list of values evaluate to `NULL`. The behaviour I'm attempting to emulate would evaluate `('a', 'b', 'c')` to `NOT NULL`. I'm trying to avoid creating another variable (e.g. :myListFlag) which would return `''` if the list was empty.