Showing posts with label COALESCE. Show all posts
Showing posts with label COALESCE. Show all posts

Saturday, April 10, 2010

Intercept null value on a query

These last days working to create a Pentaho report with openERP data, I came across a serious problem:
if a query return a null value arithmetic operations are in error. The question was how to intercept the null value, surfing on Google I found this
SELECT IFNULL (1,0) this query return 1
SELECT IFNULL(null, 0) this query return 0
Fine my problem was solved NO!!  openERP use postgresql and postgresql doesn't support IFNULL
Again surfing on Google and I found COALESCE
The postgresql manual say:
The COALESCE function returns the first arguments of ITS THAT IS NOT NULL. Returned is null only if all arguments are null. Often it is used to substitute a default value for null values 

Finally my problem was solved my query now  is something like this
SELECT COALESCE(sum(aal.amount * -1) , 0) FROM account_analytic_line AS aal WHERE (aal.account_id=83) and aal.journal_id = 3