Query Report - unsupported format character '"'

ERPNext Reports
1

I am trying to create Query Report and i am using this SQL:

SELECT u.email,
SUBSTRING_INDEX(t.subject, "-", -1) as subject ,
t.`exp_start_date`,
t.closing_date,
sum( DATEDIFF(t.closing_date,t.`exp_start_date`)) as dif,
count(u.email) as cont ,
(sum( DATEDIFF(t.closing_date,t.`exp_start_date`))/count(u.email)) as avr

FROM `tabTask` as t , tabUser as u
where t.`_assign` IS NOT NULL
AND t.`_assign` LIKE concat("%",u.email,"%") 

and SUBSTRING_INDEX(t.subject, "-", -1) = ' Data Entry'
group by u.email,SUBSTRING_INDEX( t.subject,  "-", -1 )

but when I run the report I get this error:

File "/home/mohd/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 238, in execute query = query % args ValueError: unsupported format character '"' (0x22) at index 355

This SQL is working fine in phpmyadmin but but in Query Report it is giving error!

2

I found the solution :slight_smile:

SELECT u.email as “User:Link/User:200”,
(sum( DATEDIFF(t.closing_date,t.exp_start_date))/count(u.email)) as Average

FROM tabTask as t , tabUser as u
where t._assign IS NOT NULL
AND t._assign LIKE concat(“%%”,u.email,“%%”)

and SUBSTRING_INDEX(t.subject, “-”, -1) = ’ Data Entry’
group by u.email,SUBSTRING_INDEX( t.subject, “-”, -1 )