db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: NULL checking in Derby
Date Wed, 31 Oct 2007 10:39:11 GMT
>>>>>>>>>>>> Bernt M. Johnsen wrote (2007-10-31 09:48:14):
> Hi,
> 
> >>>>>>>>>>>> Soumen Saha wrote (2007-10-26 22:36:19):
> > DELETE FROM QUIZ_OPTIONS_INFO WHERE OPTION_TEXT IN ('',null);
> > 
> 
> This statement is strictly not allowed in SQL since the data type of
> null is not determinable. You should write something like
> 
> DELETE FROM QUIZ_OPTIONS_INFO WHERE OPTION_TEXT IN ('',CAST(null AS VARCHAR(10)));

Well, this statement should do the same as your statement did vs. SQL
server, but I'm not sure if it does what you want.

IN implies an equality test, and NULL is not equal NULL in SQL (NULL =
NULL does not evaluet to TRUE, but to UNKNOWN). The statement will
therefore only delete the rows where OPTION_TEXT is equal to ''.  If
you also want to delete the rows where OPTION_TEXT is NULL, you have
to write

DELETE FROM QUIZ_OPTIONS_INFO WHERE 
       OPTION_TEXT = '' OR OPTION_TEXT IS NULL;



> 
> 
> > This is working in SQL Server but not in Derby... The error is Error code
> > -1, SQL state 42X01: Syntax error: Encountered "NULL" . Could some one
> > please tell the solution... My aim is to delete all the rows where the
> > OPTION_TEXT is empty..
> > 
> > Thanks & Regards,
> > Soumen
> 
> -- 
> Bernt Marius Johnsen, Database Technology Group, 
> Staff Engineer, Derby/Java DB
> Sun Microsystems, Trondheim, Norway



-- 
Bernt Marius Johnsen, Database Technology Group, 
Staff Engineer, Derby/Java DB
Sun Microsystems, Trondheim, Norway

Mime
View raw message