Zum Inhalt springen
Menü
Sie müssen registriert sein, um mit der Community zu interagieren.
Diese Frage wurde gekennzeichnet
2 Antworten
6066 Ansichten

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
Verwerfen
Beste Antwort

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
Verwerfen
Autor

Thanks Ahmed M.Elmubarak ..

Beste Antwort

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
Verwerfen
Autor

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

Verknüpfte Beiträge Antworten Ansichten Aktivität
1
Sept. 15
10470
3
Aug. 15
3294
2
Juli 15
4969
2
Mai 15
5333
0
März 15
3449