ISNULL function in hsql

grimpi · **Joined:** Wed May 07, 2008 12:15 pm **Posts:** 2

Hi!
Anybody knows if in HSQL, there is a function like ISNULL (SQL Server) or NVL (Oracle)??

ccsilva · **Joined:** Fri Nov 02, 2007 9:27 am **Posts:** 5

Hi,

Yes... it is possible.

See a sample:

Code:

String str_query = "select t from Task t where t.user is null";

t.user is null

Bye!

Claudiney

grimpi · **Joined:** Wed May 07, 2008 12:15 pm **Posts:** 2

Thanks Claudiney!, but I don't want filter the null values.

I want replace the null value with another value, like this:

SELECT IsNull(T.Name,'N/A') FROM T

buntu · **Joined:** Thu Oct 23, 2008 1:18 am **Posts:** 1

@grimpi,

You can use a custom defined "IsNull" function. Then you can create an alias in HSQLDB (CREATE ALIAS IsNull "package.FinalClass.isNull") and use it exactly like you mentioned.

Make sure the method you defined is static, as HSQLDB can only call static methods (as functions).

Here is a good tutorial on how to do this: http://blog.dotkam.com/2008/10/22/use-hsqldb-functions-with-hibernate-mapping-files/

-- buntu

sethrohit1977 · **Joined:** Mon Jan 22, 2007 10:32 am **Posts:** 101

I think you can use nullIf method in HQL.

chuck.lowery · **Joined:** Fri Jun 24, 2011 11:21 am **Posts:** 2

If you are going to use hsqldb you can also look through the already defined list of functions:http://hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html

Quote:

IFNULL

ISNULL

IFNULL | ISNULL ( <value expr 1>, <value expr 2> )

Returns <value expr 1> if it is not null, otherwise returns <value expr 2>. The type of the return value is the type of <value expr 1>. Almost equivalent to SQL Standard COALESCE(<value expr 1>, <value expr 2>) function, but without type modification. (JDBC)