I've a table in my database for which I need to check if all rows have one field not null.
If there are no row or if there is at least 1 row with the field null => true
If there are rows and they are all with the field not null => False
Is there a way to do this in on simple query? Or I need to check if my table is empty first then if it's not check if I've a row with the field value empty ?
count(field) and compare the two:
select case when count(*) > 0 and count(*) = count(field) then 1 -- not empty and no nulls else 0 end as isgood from mytable;
Oracle SQL has no boolean data type , so I use 1 for true and 0 for false. You can replace this with whatever you like (e.g. 'true' instead of 1 and 'false' instead of 0).
As to turning this into a predicate (correlated to a main query), you'd use something along the lines of:
select ... from main where exists ( select 1 from mytable where mytable.colx = main.coly having count(*) > 0 and count(*) = count(field) );