Details
-
Type:
Bug
-
Status: Open
-
Priority:
Minor
-
Resolution: Unresolved
-
Affects Version/s: 2.5
-
Fix Version/s: None
-
Component/s: Block: Configurable report
-
Labels:None
-
Affected Branches:MOODLE_25_STABLE
Description
Adding a filter to a SQL report causes a SQL error if the query does not have a where clause in it.
For example, add a "User field search box" filter on the filter tab and at the bottom of your sql (%%FILTER_USERS:ex.lastname%%).
Sample query:
SELECT *
FROM PREFIX_EXPORT ex
%%FILTER_USERS:ex.lastname%%
This will cause an error:
Error reading from database
More information about this error
Debug info: ORA-00907: missing right parenthesis
SELECT *
FROM (SELECT *
FROM m_EXPORT ex
AND ex.lastname LIKE '%Fisher%')
WHERE rownum <= :o_oracle_num_rows
[array (
'o_oracle_num_rows' => 5000,
)]
Error code: dmlreadexception
Stack trace:
line 423 of /lib/dml/moodle_database.php: dml_read_exception thrown
line 272 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->query_end()
line 1088 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()
line 54 of /blocks/configurable_reports/reports/sql/report.class.php: call to oci_native_moodle_database->get_recordset_sql()
line 86 of /blocks/configurable_reports/reports/sql/report.class.php: call to report_sql->execute_query()
line 67 of /blocks/configurable_reports/viewreport.php: call to report_sql->create_report()
A work-around is to add a "WHERE 1=1":
SELECT *
FROM PREFIX_EXPORT ex
WHERE 1=1
%%FILTER_USERS:ex.lastname%%