Hi,

I have a requirement to check whether IS [NOT] NULL function like anything in DB2, to check whether it is NULL

in oracle we can use
SELECT a FROM abc
WHERE IS NOT NULL

is there any thing similar in DB2 which can be used in a WHERE clause
please help

Saiju

yes, you can do the same in DB2

however, your oracle example is not quite right --this is incorrect in any database system

it should be like this --

My tutor once said: "think of that little river in austria: the INN" when I coded someting like "not is null" which was rejected as valid SQL

thanks for the reply & correction

SELECT a FROM abc
WHERE smething IS NOT NULL


is fetching NULL values as well in 9.7.

Is there any specific setting needs to be done ..?

what is the exact query you ran, please?

The below query returns null values in DB2 9.7

select NEG_INSTR_SER_NO from journl_entry_item where NEG_INSTR_SER_NO is not null

Any other alternative to check null values ..?

Are you confusing empty strings with nulls, may be?

not his fault, really, coming from oracle which doesn't know the difference

the same dataset behaves differently in oracle and DB2 for the same query ..
i am just trying to find the difference in implementation

How the DB2 differentiate an empty string compared to oracle ..?

an empty string (null string/string with length zero) is = '' (or = "")

null is a column attribute. a column may contain a
'null string/empty string/varchar length 0/fixed length all spaces'
but if a column is null, then there is no value/data to check, you check the attribute,
which in db2 is IS COLUMN NULL.

do you mean you loaded data from a file into a DB2 table and you are now trying to find records that are not null and you are finding records that should be null? If that is the case, then you should look at your load control statement to see if the null indicator value was identified for the load process.
Dave

In Oracle, testing = '' (two single quotes) is the same as testing for null. This is not in accordance with ANSI SQL standards and is not supported by normal DB2 syntax, however, I am not sure about Oracle compatibility mode in DB2 9.7.