Query report - how to use wild card?

ERPNext Reports
1

I would like to understand the syntax for Query report to use wild card, similar to using the ‘like’ filter in the Report Builder.

For e.g.,

SELECT
inv.name
FROM
tabSales Invoice as inv
WHERE
inv.customer_group = ‘Signage’
OR inv.customer_name like %Bharat%
OR inv.customer_name like %Hindustan%
OR inv.customer_name like %Indian%

Obviously the OR condition is giving an error, I would like to know the correct syntax?

Is there any documentation on the Query report syntax, it seems to be different from the usual “SQL Query” syntax.

2 Likes
2

select inv.name from tabSales Invoice as inv where inv.customer_group = ‘Signage’ or inv.customer_name like ‘%bharat%’ or inv.customer_name like ‘%Hindustan%’ or inv.customer_name like ‘%Indian%’;

3

Tried that just now and getting an error!

4

5

@rmehta
a) Can you please point me to the documentation of Query report syntax?
b) How to use wild card, similar to ‘like’ filter in Query reports?

Thanks,
Vivek

6

Hi all,

I see this is an old thread, but I think the topic still stands… When using a wildcard (%) in a query report, it crashes. Example:

SELECT * FROM `tabSales Invoice` WHERE `customer` LIKE '%a%';

returns

Traceback (most recent call last):
  File "/home/frappe/frappe-bench/apps/frappe/frappe/app.py", line 62, in application
    response = frappe.handler.handle()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/handler.py", line 22, in handle
    data = execute_cmd(cmd)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/handler.py", line 53, in execute_cmd
    return frappe.call(method, **frappe.form_dict)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/__init__.py", line 939, in call
    return fn(*args, **newargs)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/desk/query_report.py", line 90, in run
    result = [list(t) for t in frappe.db.sql(report.query, filters)]
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database.py", line 166, in sql
    self._cursor.execute(query, values)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py", line 163, in execute
    query = self.mogrify(query, args)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py", line 142, in mogrify
    query = query % self._escape_args(args, conn)
ValueError: unsupported format character 'a' (0x61) at index 57

Tried to encode as html %, but that does not help…

7

Found the solution, posting it for reference if someone else is looking for it… Use double %% to bypass Python:

SELECT * FROM `tabSales Invoice` WHERE `customer` LIKE '%%a%%';
7 Likes