Hi David and all,
I looked around and it COALESCE is an ANSI SQL feature that is supported by
oracle, postgresql, mysql and sql server. I tried directly by hand using
postgresql and mysql.
Still it is necessary to test this across every possible database. I have a
patch ready that implements the feature. In particular, the way it is
implemented (surrounds the default value in single quotations) needs to be tested.
I'll update the jira with a patch and notes on the implementation quirk.
This could be used for regular <alias> fields as well, but for now the patch
implements it for <complex-alias> only.
- Leon
David E Jones wrote:
It would be great to see this issue addressed, but a couple of thoughts
about it:
- we need to find out if without the coalesce function this happens in
_any_ other databases, ie some databases (well, all databases) have
funny quirks like this of some sort or other
- it would be good to find out of the coalesce function exists in any
other databases; I have a good book on this topic at home (the SQL
reference from Oreilly that covers 5 different databases), but it will
be about three weeks before I'll within a few thousand miles of that...
- chances are this is a PostgreSQL quirk in which case we'll probably
want to just have this be the default behavior if an attribute
specifically for it is set on the datasource element in the
entityengine.xml file, like various other attributes we have set there
to make things work more consistently on the application side of the
entity engine
-David
On Sep 27, 2006, at 6:00 PM, Leon Torres (JIRA) wrote:
Improve complex-alias to handle nulls and defaults
--------------------------------------------------
Key: OFBIZ-336
URL: http://issues.apache.org/jira/browse/OFBIZ-336
Project: OFBiz (The Open for Business Project)
Issue Type: Improvement
Components: framework
Reporter: Leon Torres
Priority: Minor
The idea behind complex-alias is useful for certain things, but the
way it works now is not useful. Suppose you build a simple addition
rule. If any variable in the equation is null, the entire alias
resolves to null.
For example, suppose you create a view entity to report quantities on
OrderItem. The query would be:
select product_id, (quantity - cancel_quantity) from order_item;
However if you run this, you'll see that where quantity or
cancel_quantity are null, the result is null. A solution in
postgresql is to use the coalesce function to specify a default value:
select product_id, (coalesce(quantity, 0) - coalesce(cancel_quantity,
0)) from order_item;
Now the query works.
So the fix I think is to add another attribute
default-value="something". If the default-value is present, then add
the coalesce function. Not sure whether this is standard sql or not.
I also encountered this issue for concatenation || operator, so I
think it affects most operations.
--This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the
administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
