Hi,
I'm using OpenLMS (Moodlerooms) 3.5 and trying to build a SQL query to generate an admin report with:
Student first name | Last name | Org ID number (custom profile) | Course name | Completion date.
I do not know SQL and for some reason, none of the SQL syntax from the adhoc contributed reports website works. Obviously I'm doing something wrong or OpenLMS/Moodlerooms requires a slightly different format of those queries? Keep getting the error "The SQL entered is not supported by this report or is incorrect. Error reading from database".
After many tries, the following format of code works, so can someone please guide on how to change this code to pull the above 5 pieces of data? Thanks very much! PS
SELECT d.userid AS userid,
MAX(CASE WHEN f.shortname = 'Field1 Short Name' THEN d.data ELSE '' END) AS FieldName1,
MAX(CASE WHEN f.shortname = 'Field2 Short Name' THEN d.data ELSE '' END) AS FieldName2
FROM mdl_user_info_data d
JOIN mdl_user_info_field f ON d.fieldid = f.id
GROUP BY d.userid