CODEWITHSUNDEEP
javasqldb2
SID
SID

Reputation: 680

filter for null value not working for DB2 and Sybase

The Java based Application is using common select query to extract records from all databases. The records with null values for a particular column(here 'id') should get dropped.

This is the query:

SELECT * from Employee WHERE 1=1 AND id IS NOT NULL AND cast(id as varchar) !=''

It works fine for all databases but gives SQLException for DB2.

The goal is :

  1. Check if null and blank value is considered same in DB2/Sybase.
  2. Modify the SQL query to make it work for DB2/Sybase

Upvotes: 0

Views: 1925

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Although this won't affect your problem, I would use the <> operator instead of != (the former is definitely part of the standard; I'm not sure if newer versions of the standard insist on != being supported):

SELECT *
from Employee
WHERE 1=1  AND id IS NOT NULL AND id <> '';

The next suspicious thing is comparing an id to a string. If id is a string, then no problem. However, if it is numeric, you might have a type incompatibility. Try one of these where clauses:

WHERE 1=1  AND id IS NOT NULL AND id <> 0;

or

WHERE 1=1  AND id IS NOT NULL AND cast(id as varchar(255)) <> '';

Upvotes: 0

Related Questions