You are likely referring to the recent question Counting a row's NULL columns. (Note: that title is a bit misleading....
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
It should actually have been Counting a row's NOT NULL columns.)
If your table is similar to the sample table in that question, then the answer is the same: you shouldn't need to write that horrendous series of CASE expressions for each column. Normalize the table, so that you have a single column with multiple rows, instead of multiple columns in one row. Then the counting becomes trivial:
select count(columnx) as not_null_columns from TableB where id = 937
Notice that this gives the non-NULL count (it's the same query from the previous question). The trick for counting NULL values—if you can call it a trick—is that the normalized table would typically not have a row for a NULL, i.e. a value that isn't there! In the unnormalized table, you can count the number of NULLs using a similarly horrendous series of CASE expressions, with IS NULL instead of IS NOT NULL. In the normalized table, there are no NULLs at all, so there is nothing to count!
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.