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.