Sagewire Logo

JAVADB / Apache Derby equivalent to the SQL Server IsNull, Oracle nvl and MySQL IfNull

9 Message(s) by 4 Author(s) originally posted in java databases


From: James Appleby Date:   Friday, August 10, 2007
I'm trying to add support for Apache Derby to a piece of software that
already has support for Oracle, SQL Server and MySQL. There is a lot
of DB specific coding involved to get the best performance possible.
Several of these queries involve checking to see if a nested query
returns a value and substituting 0 if no rows are returned.

In Oracle the function is called: nvl
In SQL Server the function is called: IsNull
In MySQL the function is called: IfNull

I can not find an equivalent for Derby. Does one exist?


From: Thomas Kellerer Date:   Friday, August 10, 2007
wrote in message on 10.08.2007 17:48:
I'm trying to add support for Apache Derby to a piece of software that
already has support for Oracle, SQL Server and MySQL. There is a lot
of DB specific coding involved to get the best performance possible.
Several of these queries involve checking to see if a nested query
returns a value and substituting 0 if no rows are returned.
In Oracle the function is called: nvl
In SQL Server the function is called: IsNull
In MySQL the function is called: IfNull
I can not find an equivalent for Derby. Does one exist?


CASE or NULLIF

http://db.apache.org/derby/docs/10.2/ref/rrefcasenullif.html

Thomas


From: James Appleby Date:   Tuesday, August 14, 2007
wrote in message:
CASE or NULLIF
http://db.apache.org/derby/docs/10.2/ref/rrefcasenullif.html



Thank you for replying. I had investigated these options and I do not
think they offer what I need.

NULLIF returns a null if meeting a boolean condition, I need to return
0 when it is null.

CASE could give the result but'd require the query to be done
twice, once for boolean evaluation and then again to return the
value. If I can only get the value by run ning the query twice, I
might as well do a union. My hope was that a function existed that
meant I did not need to do that.James


From: Thomas Kellerer Date:   Tuesday, August 14, 2007
wrote in message:
wrote in message:
CASE or NULLIF

http://db.apache.org/derby/docs/10.2/ref/rrefcasenullif.html

Thank you for replying. I had investigated these options and I do not
think they offer what I need.
NULLIF returns a null if meeting a boolean condition, I need to return
0 when it is null.
CASE could give the result but'd require the query to be done
twice, once for boolean evaluation and then again to return the
value. If I can only get the value by running the query twice, I
might as well do a union. My hope was that a function existed that
meant I did not need to do that.



Why'd you need to run the Query twice when using CASE?

Can you give an example of what you are trying to do? I'm pretty sure
that I could replace Oracle's nvl with a CASE without running a
statement twice.

Thomas


From: James Appleby Date:   Tuesday, August 14, 2007
This is the DB I'm looking at. (Many table s and columns excluded for
simplicity.)

TASK {taskid}
PROCESSTASK {runid, taskid, processid, startstamp, stopstamp}

I have a number of tables representing tasks that can be run multiple
times. Tasks are made up of one or more processes. The table I am
querying on records the start and stop times of the processes. The
purpose is to get the count of currently running processes.select sc.scriptid, nvl((select count(pt.runid)
from processtask pt
where pt.runid = <RUN_ID>
and pt.stopstamp is null
group by pt.taskid), 0)
from task tk where tk.taskid in (<TASK_IDS>)


From: Thomas Kellerer Date:   Tuesday, August 14, 2007
wrote in message on 14.08.2007 17:57:
This is the DB I'm looking at. (Many tables and columns excluded for
simplicity.)
TASK {taskid}
PROCESSTASK {runid, taskid, processid, startstamp, stopstamp}
I have a number of tables representing tasks that can be run multiple
times. Tasks are made up of one or more processes. The table I am
querying on records the start and stop times of the processes. The
purpose is to get the count of currently running processes.
select sc.scriptid, nvl((select count(pt.runid)
from processtask pt
where pt.runid = <RUN_ID>
and pt.stopstamp is null
group by pt.taskid), 0)
from task tk where tk.taskid in (<TASK_IDS>)


You beat me with that ;)

Actually I tried to run this with Derby, and it seems it doesn't support the
SELECT inside the column list anyway, so you probably need to rewrite completely.

You do not have any correlation between processtask in the from clause and the
calculation of the number of runids. Is that intended?
So, I'm not sure I understood your query completely (the table for the alias sc
is also missing), but maybe something like this:

SELECT tk.*,
c.num_procs
FROM task tk,
(SELECT pt.taskid AS taskid,
COUNT(pt.runid) AS num_procs
FROM processtask pt
WHERE pt.runid = 1
GROUP BY pt.taskid) c
WHERE c.taskid = tk.taskid

would do the job as well (no need for CASE or nvl whatsoever). But as I said, I
do not think I understood your query completely

Regards
Thomas


From: Dyreatnews Date:   Monday, August 27, 2007
James Appleby <james.w.appleby@xxxxxxxxxxx> writes:

I'm trying to add support for Apache Derby to a piece of software that
already has support for Oracle, SQL Server and MySQL. There is a lot
of DB specific coding involved to get the best performance possible.
Several of these queries involve checking to see if a nested query
returns a value and substituting 0 if no rows are returned.
In Oracle the function is called: nvl
In SQL Server the function is called: IsNull
In MySQL the function is called: IfNull
I can not find an equivalent for Derby. Does one exist?



Try subscribing to derby-user and ask the question there.

--
dt


From: Lew Date:   Monday, August 27, 2007
James Appleby <james.w.appleby@xxxxxxxxxxx> writes:
I'm trying to add support for Apache Derby to a piece of software that
already has support for Oracle, SQL Server and MySQL. There is a lot
of DB specific coding involved to get the best performance possible.
Several of these queries involve checking to see if a nested query
returns a value and substituting 0 if no rows are returned.

In Oracle the function is called: nvl
In SQL Server the function is called: IsNull
In MySQL the function is called: IfNull

I can not find an equivalent for Derby. Does one exist?



Have you tried the Derby documentation?

First, there's the standard SQL CASE construct, then there's the equivalent
Derby function
<http://db.apache.org/derby/docs/10.3/ref/rrefcasenullif.html>

Manuals are a wonderful thing, are not they? I have no familiarity with Derby
and it took about five minutes to find.

--
Lew


From: Thomas Kellerer Date:   Monday, August 27, 2007
wrote in message:
Have you tried the Derby documentation?
First, there's the standard SQL CASE construct, then there's the
equivalent Derby function
<http://db.apache.org/derby/docs/10.3/ref/rrefcasenullif.html>
Manuals are a wonderful thing, are not they? I have no familiarity with
Derby and it took about five minutes to find.



I posted that link already and indeed nullif is *not* the function the
James is looking for.

Actually I claimed that I could replace any occurance of nvl() with a
case, but his example (posted on 14.08.2007 17:57) showed me that it's
not that easy ;)

Thomas



Next Message: (Mis)use of transactions


Blogs related to JAVADB / Apache Derby equivalent to the SQL Server IsNull, Oracle nvl and MySQL IfNull

JavaDB / Apache Derby equivalent to the SQL Server IsNull and ...
Firstly, if questions for JavaDB are supposted to go somewhere else, sorry for posting in the JDBC section, but I couldn't find a specific section.


Programming | Sports | Autos

copyright 2006
Valid XHTML 1.0 Transitional