Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle SQL Syntax - Check multiple columns for IS NOT NULL

Tags:

syntax

sql

oracle

Is there a more concise syntax for checking whether multiple columns are not null? I'm looking to simplify

weight IS NOT NULL AND
weight_unit IS NOT NULL AND
length IS NOT NULL AND
width IS NOT NULL AND
height IS NOT NULL AND
dimensional_unit IS NOT NULL

into something shorter.

Using Oracle, if it's relevant.

like image 500
jbreed Avatar asked Dec 21 '11 20:12

jbreed


People also ask

How do I find not null columns in a table?

Depending on the privilege, you need to look into [DBA|USER|ALL]_TAB_COLUMNS. ALL_TAB_COLUMNS Column Datatype Description NULLABLE VARCHAR2(1) Indicates whether a column allows NULLs. The value is N if there is a NOT NULL constraint on the column or if the column is part of a PRIMARY KEY.


1 Answers

With De Morgan's law:

NOT (A OR B) = (NOT A) AND (NOT B)

you save 20 chars ;)

NOT (
weight IS NULL OR
weight_unit IS NULL OR
length IS NULL OR
width IS NULL OR
height IS NULL OR
dimensional_unit IS NULL 
)
like image 141
dani herrera Avatar answered Oct 14 '22 20:10

dani herrera