not null in all columns

Moderator: NorbertKrupa

Post Reply
bhupal
Newbie
Newbie
Posts: 11
Joined: Wed Apr 03, 2013 9:30 am

not null in all columns

Post by bhupal » Thu Apr 25, 2013 5:41 am

hi all,

i need to check that, not null records in all columns.

for single column we can write query like

select *from table_name where not is null (id);

if i have 10 columns and i need to retrive records those are not having null in all columns.

please give me query for this.

thanks in advance.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: not null in all columns

Post by JimKnicely » Thu Apr 25, 2013 12:30 pm

Maybe something like the following can help you out?

Code: Select all

dbadmin=> create table null_test (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int, c9 int, c10 int);
CREATE TABLE

dbadmin=> insert into null_test values (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into null_test values (1, 2, 3, 4, 5, 6, 7, 8, 9, null);
 OUTPUT
--------
      1
(1 row)
Then you could concat the columns and check the result is not null:

Code: Select all

dbadmin=> select *
dbadmin->   from null_test
dbadmin->  where (c1 || c2 || c3 || c4 || c5 || c6 || c7 || c8 || c9 || c10) is not null;
 c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | c10
----+----+----+----+----+----+----+----+----+-----
  1 |  2 |  3 |  4 |  5 |  6 |  7 |  8 |  9 |  10
(1 row)
Or a more verbose method might be:

Code: Select all

dbadmin=> select *
dbadmin->   from null_test
dbadmin->  where c1 is not null
dbadmin->    and c2 is not null
dbadmin->    and c3 is not null
dbadmin->    and c4 is not null
dbadmin->    and c5 is not null
dbadmin->    and c6 is not null
dbadmin->    and c7 is not null
dbadmin->    and c8 is not null
dbadmin->    and c9 is not null
dbadmin->    and c10 is not null;
 c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | c10
----+----+----+----+----+----+----+----+----+-----
  1 |  2 |  3 |  4 |  5 |  6 |  7 |  8 |  9 |  10
(1 row)
Hope this helps!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica Database Development”