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

[James Appleby]

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't find an equivalent for Derby. Does one exist?

[Thomas Kellerer]

CASE or NULLIF

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

Thomas

[James Appleby]

On 10 Aug, 17:19, Thomas Kellerer <FJIFALSDG...@spammotel.com> wrote:
>
> 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 don't
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 would 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 didn't need to do that.

James

[Thomas Kellerer]

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

[James Appleby]

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

[Thomas Kellerer]

You beat me with that ;)

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

You don't 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
don't think I understood your query completely

Regards
Thomas

[Dyrea...@sun.com]

James Appleby <james.w...@gmail.com> writes:

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

--
dt

[Lew]

James Appleby <james.w...@gmail.com> 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't 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, aren't they? I have no familiarity with Derby
and it took about five minutes to find.

--
Lew

[Thomas Kellerer]

Lew wrote:
>
> 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, aren't 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