Skip to Content
Menu
You need to be registered to interact with the community.
This question has been flagged
2 Odgovori
6073 Prikazi

I am working With Odoo ERP and I want to restrict for return None Value from the List of Dictionary from the generate SQL Query.

form_data = self.read(cr, uid, ids, ['start_date','end_date'], context=context)[0]
sql = "select i.date_invoice, i.number, (select name from res_partner where id=i.partner_id) as partner,i.currency_id, (select name from res_currency where id=i.currency_id) as currency, (select description from account_tax where name=t.name), t.amount, t.base, (t.amount+t.base) as total from account_invoice i, account_invoice_tax t where t.invoice_id = i.id and i.state = 'open' and i.type = 'out_invoice' and i.date_invoice >= '%s' and i.date_invoice <= '%s' order by t.name"%(form_data['start_date'],form_data['end_date']) 

cr.execute(sql)
data = cr.dictfetchall()

Generated Result :

data=[

{'currency_id': 38, 'description': u'GST 7%', 'number': u'SAJ/2015/0021', 'date_invoice': '2015-05-05', 'currency': u'SGD', 'amount': 283.08, 'base': None, 'partner': u'partner1', 'total': None},

{'currency_id': 38, 'description': None, 'number': u'SAJ/2015/0021', 'date_invoice': '2015-05-05', 'currency': u'SGD', 'amount': 283.08, 'base': 4044.0, 'partner': u'partner1', 'total': 4327.08},

{'currency_id': 38, 'description': None, 'number': u'SAJ/2015/0020', 'date_invoice': '2015-05-04', 'currency': u'SGD', 'amount': 0.0, 'base': 3550.0, 'partner': u'EAST MARINE PTE LTD', 'total': 3550.0},

{'currency_id': 38, 'description': None, 'number': u'SAJ/2015/0021', 'date_invoice': '2015-05-05', 'currency': u'SGD', 'amount': 0.0, 'base': 500.0, 'partner': u'partner1', 'total': 500.0},

{'currency_id': 38, 'description': None, 'number': u'SAJ/2015/0023', 'date_invoice': '2015-05-05', 'currency': u'SGD', 'amount': 0.0, 'base': 11100.0, 'partner': u'partner2', 'total': 11100.0}

]

Above Generated List of Dictionary Values found the description and total keys of values got the None result using above SQL Query.

I do not want None Value but instead of None i want to blank string or other blank space(empty string).

What should i do for make restriction on None Value. ?

Avatar
Opusti
Best Answer

Hello,

You can use coalesce in your sql as:

sql = """SELECT coalesce(description, '') AS description FROM your_table"""

You can also have an eye on coalesce on the documentation 

Hope this could helps

Avatar
Opusti
Avtor

Thanks Ahmed M.Elmubarak ..

Best Answer

Hello Chaitanya,

According to me, you can try CASE WHEN in your query like 

CASE WHEN t.base is not null THEN t.base ELSE 0.0 END

I hope that will work.

Thanks!

Avatar
Opusti
Avtor

Thanks a lot Krutarth Buch for another way for solve my problem

Related Posts Odgovori Prikazi Aktivnost
1
sep. 15
10473
3
avg. 15
3300
2
jul. 15
4977
2
maj 15
5345
0
mar. 15
3457