How can I check whether there is a value set for a particular field in a table ?
Thank You
-
Nimo - do you mean how do you find whether there is a corresponding look-up table for a particular column, or whether a particular column contains any value or not? Senthil is getting downvoted but I've a feeling he's probably closest to what you're after.Nick Pierpoint– Nick Pierpoint2010-05-20 15:34:40 +00:00Commented May 20, 2010 at 15:34
Add a comment
|
4 Answers
.... WHERE field IS NOT NULL ....
Comments
We can run a query checking on NOT NULL in the WHERE clause. I'm using count for convenience.
SQL> select count(*)
2 from emp
3 where comm is not null
4 /
COUNT(*)
----------
4
SQL>
So that's four rows where COMM has a value set.
If we want to test for the presence of a value in the projection, then we can use CASE(), DECODE() or one of Oracle's NULL-related functions. For instance, this statement wraps a call to [NVL2()][2] in a SUM() to count how many instances of COMM are NULL and NOT NULL in the same query.
SQL> select sum(nvl2(comm, 1, 0)) as notnull_cnt
2 , sum(nvl2(comm, 0, 1)) as null_cnt
3 from emp
4 /
NOTNULL_CNT NULL_CNT
----------- ----------
4 16
SQL>
Comments
you can use this query,
select * from table_name where column_name='field value';
2 Comments
professionalcoder2010
If the values are returned means then there were some values set..if nothing returned means..then value is not set..In this thought I posted.What is wrong in it?
Nick Pierpoint
There's a language issue I think. You're reading it (I think probably correctly) as "is this value part of a defined set of values a value-set. Everyone else is reading it as "has a value been set or not".
This way:
SELECT whatever FROM table WHERE that_field IS NULL
Or replace IS NULL with IS NOT NULL if you want to select rows where the field has a value.
Comments
