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
