Developing a new query report in ERPNext V-14. wrote a SQL query and it works well but when adding a filter fieldname and keeping it empty in value then error message occur “KeyError:” whereas if write or choose any value in that field it work well. what I want, if no value in fieldname then it should not consider in where statement in the SQL.
I believe, the error reason is the fieldname/object not found or not initializing due to no value and since it is checking in WHERE statement therefore, this error is occurring but not sure from actual reason.
I need to know is there any way to fix this? i do not want to make fieldname mandatory.
Here is the SQL:
SELECT
j.company as company
, ja.account as account
, ja.debit as debit
, ja.credit as credit
FROM tabJournal Entry as j
inner join tabJournal Entry Account as ja on ja.parent=j.name
where ja.account=%(account)s ;
expecting to do something like this or workaround to check either fieldname/object exists or not? similarly like below SQL
select … from
where exists(%(account)s) or isnull(%(account)s)>0
furthermore, refer the below screenshot:
