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 that error 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
FROMtabJournal Entry
as j
inner jointabJournal 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 this
select … from
where exists(%(account)s)
or isnull(%(account)s)>0 ;
furthermore, refer the below screenshot: