You are viewing a plain text version of this content. The canonical link for it is here.

Posted to general@db.apache.org by Soumen Saha <so...@gmail.com> on 2007/10/27 02:43:00 UTC

NULL checking in Derby

DELETE FROM QUIZ_OPTIONS_INFO WHERE OPTION_TEXT IN ('',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...

Thanks & Regards,
Soumen

Re: NULL checking in Derby

Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.

>>>>>>>>>>>> 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

Re: NULL checking in Derby

Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.

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)));


> 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

Re: NULL checking in Derby

Posted by Peter Ondruška <pe...@gmail.com>.

DELETE FROM quiz_options_info WHERE option_text IS NULL

On 27.10.2007, at 4:36, Soumen Saha wrote:

> DELETE FROM QUIZ_OPTIONS_INFO WHERE OPTION_TEXT IN ('',null);

NULL checking in Derby

Posted by Soumen Saha <so...@gmail.com>.

DELETE FROM QUIZ_OPTIONS_INFO WHERE OPTION_TEXT IN ('',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

Re: NULL checking in Derby

Posted by Soumen Saha <so...@gmail.com>.

Hi Brayan,

Could you do in another way? My aim is to delete all the rows where the
OPTION_TEXT is empty..

Regards,
Soumen

On 10/26/07, Bryan Pendleton <bp...@amberpoint.com> wrote:
>
> Soumen Saha wrote:
> > DELETE FROM QUIZ_OPTIONS_INFO WHERE OPTION_TEXT IN ('',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...
>
> Hi Soumen,
>
> I'm afraid I don't know the answer.
>
> I recommend you try asking on derby-user@db.apache.org. I think you
> will find a lot more people on that list who know about Derby.
>
> thanks,
>
> bryan
>

Re: NULL checking in Derby

Posted by Bryan Pendleton <bp...@amberpoint.com>.

Soumen Saha wrote:
> DELETE FROM QUIZ_OPTIONS_INFO WHERE OPTION_TEXT IN ('',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...

Hi Soumen,

I'm afraid I don't know the answer.

I recommend you try asking on derby-user@db.apache.org. I think you
will find a lot more people on that list who know about Derby.

thanks,

bryan