How to replace null into 0 in query

Classic Servoy
1

Hi,
I need to calculate total amount as (quantity + ship+cancel) * price. The ship or cancel fields can be null. What function I can use to replace NULL with 0 ?

Here a samples which give me error:
var vServer = databaseManager.getDataSourceServerName(controller.getDataSource())
var arg = new Array();
arg[0] = 21;
_query = "select price * (quantity+((isNaN(ship) ? 0 : ship) + ((isNaN(cancel) ? 0 : cancel) ) from orderdetail where orderid = ? "
var _dset = databaseManager.getDataSetByQuery(vServer, _query, arg, 10000);

If use QB Builder it doesn’t give error but result wrong ( if for example, ship is null and cancel not null the result will be calculation of price * qty and cancel was not included in calculation)

var q1 = databaseManager.createSelect(‘db:/pcd/orderdetail’)
q1.where.add(q1.columns[‘orderid’].eq(vOrderID))
q1.result.clear()
q1.result.add(q1.columns[‘price’].multiply(q1.columns[‘quantity’].plus(isNaN(q1.columns[‘ship’]) ? 0 : q1.columns[‘ship’]).plus(isNaN(q1.columns[‘cancel’]) ? 0 : q1.columns[‘cancel’])))
var _dset1 = databaseManager.getDataSetByQuery(q1, -1)

2

PostgreSQL has a function called COALESCE. You can do this

select price * (quantity + COALESCE(shio,0) + COALESCE(cancel,0)) from orderdetail where orderid = ?
3

Works with COALESCE. Is it will works with any database? With QB Builder still have a problem. Any help will be appreciated.

Here working code for select statement:
var vServer = databaseManager.getDataSourceServerName(controller.getDataSource())
var arg = new Array();
arg[0] = 21;
_query = "select price ,quantity,COALESCE(ship,0) as ship,COALESCE(cancel,0) as cancel,price * (quantity+COALESCE(ship,0)+COALESCE(cancel,0)) as total from orderdetail where orderid = ?::integer "
var _dataset = databaseManager.getDataSetByQuery(vServer, _query, arg, 10000);

4

Leonid,

The QueryBuilder also features the coalesce function.

query.result.add(query.functions.coalesce(query.columns.contact_id, -1))

Rob