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.
not null in all columns
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: not null in all columns
Maybe something like the following can help you out?
Then you could concat the columns and check the result is not null:
Or a more verbose method might be:
Hope this helps!
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)
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)
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.