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

Thread view:

James Appleby - 10 Aug 2007 16:48 GMT

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?

Reply to this Message

Thomas Kellerer - 10 Aug 2007 17:19 GMT

James Appleby wrote 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
[quoted text clipped - 7 lines]
>
> I can't find an equivalent for Derby. Does one exist?

CASE or NULLIF

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

Thomas

Reply to this Message

James Appleby - 14 Aug 2007 11:52 GMT

> 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

Reply to this Message

Thomas Kellerer - 14 Aug 2007 12:10 GMT

>> CASE or NULLIF
>>
[quoted text clipped - 11 lines]
> might as well do a union. My hope was that a function existed that
> meant I didn't need to do that.

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

Reply to this Message

James Appleby - 14 Aug 2007 16:57 GMT

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

Reply to this Message

Thomas Kellerer - 14 Aug 2007 17:30 GMT

James Appleby wrote on 14.08.2007 17:57:

> This is the DB I'm looking at. (Many tables and columns excluded for
> simplicity.)
[quoted text clipped - 13 lines]
> 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 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

Reply to this Message

Dyreatnews@sun.com - 27 Aug 2007 13:12 GMT

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

Signature

Reply to this Message

Lew - 27 Aug 2007 13:18 GMT

James Appleby <james.w.appleby@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
[quoted text clipped - 7 lines]
>>
>> 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.

Signature

Lew

Reply to this Message

Thomas Kellerer - 27 Aug 2007 13:41 GMT

> Have you tried the Derby documentation?
>
[quoted text clipped - 4 lines]
> 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

Reply to this Message

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

Free Magazines