Funny SELECT Question

[Clayton Carter-2]

Being a little new to SQL, I have a question that I'm having a hard
time answering.  I have created a master/detail desktop application
using HSQL to handle farm and garden crop planning.  Since a number of
different crops might have very similar characteristics and only vary
in a few details, I wanted to implement some form of "data
inheritance" where a database record (record A) could be marked as
being similar to another record (record B) and any blank fields in the
record A could be filled in with data from the corresponding fields in
record B.

I have gotten this to work great in the logic of my program, which is
to say that the data structures I've created to hold and handle
individual records are able to work with this inheritance scheme quite
well.  My current problem is that the "master" list or table that is
displayed (which is just a Java TableModel wrapped around a ResultSet)
is riddled with blank cells that I would like to fill in
automatically.

Any suggestions on how to create a SELECT statement that automatically
fills in blank or NULL values with the corresponding values from
another record?

Thanks!

-------------------------------------------------------------------------
Check out the new SourceForge.net Marketplace.
It's the best place to buy or sell services for
just about anything Open Source.
http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace
_______________________________________________
hsqldb-developers mailing list
hsqldb-developers@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers

[John Yeary]

Hello Clayton,

The HSQLDB has a number of built-in functions. Please check out the documentation. It seems like you need one of two functions:

IFNULL(exp,value)

if exp is null, value is returned else exp)

COALESCE(expr1,expr2,expr3,...)^[2]

if expr1 is not null then it is returned else, expr2 is evaluated and if not null it is returned and so on

John

On Jan 11, 2008 12:10 PM, Clayton Carter <crcarter.giveout@...> wrote:

Being a little new to SQL, I have a question that I'm having a hard
time answering.  I have created a master/detail desktop application
using HSQL to handle farm and garden crop planning.  Since a number of
different crops might have very similar characteristics and only vary
in a few details, I wanted to implement some form of "data
inheritance" where a database record (record A) could be marked as
being similar to another record (record B) and any blank fields in the
record A could be filled in with data from the corresponding fields in
record B.

I have gotten this to work great in the logic of my program, which is
to say that the data structures I've created to hold and handle
individual records are able to work with this inheritance scheme quite
well.  My current problem is that the "master" list or table that is
displayed (which is just a Java TableModel wrapped around a ResultSet)
is riddled with blank cells that I would like to fill in
automatically.

Any suggestions on how to create a SELECT statement that automatically
fills in blank or NULL values with the corresponding values from
another record?

Thanks!

-------------------------------------------------------------------------
Check out the new SourceForge.net Marketplace.
It's the best place to buy or sell services for
just about anything Open Source.
http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace
_______________________________________________
hsqldb-developers mailing list
hsqldb-developers@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers

--
John Yeary
--
"If I have seen further, it is because I have stood on the shoulders of giants..."  Sir Isaac Newton
-------------------------------------------------------------------------
Check out the new SourceForge.net Marketplace.
It's the best place to buy or sell services for
just about anything Open Source.
http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace
_______________________________________________
hsqldb-developers mailing list
hsqldb-developers@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers