Hi, I'm hoping this will be quite simple for someone who is more of an expert with the syntax required for Moodle. We have a couple of courses that have been named with a numerical number at the end of the title. What we would like to do is to strip the number from the course ID. There are not many, so happy to do on an individual basis. The courseID's are not consistent in length to limit the extract to a certain number of characters. Currently I have the below SQL working fine for the adjustment of 1 course ID, however I need to be able to do this for several different courseID's.... Moodle does not seem to like 'else', or I am putting it in the wrong syntax. An example of what I need (currently getting errors) where the bold line is below:
If (c.idnumber='MBLQ1', 'MBLQ', c.idnumber) else if (c.idnumber='MBLQ2', 'MBLQ', c.idnumber) etc.... AS 'Certification Code'
SELECT DISTINCT u.username AS 'ID Number',
if (c.idnumber='MBLQ1', 'MBLQ', c.idnumber) AS 'Certification Code',
FROM_UNIXTIME(gg.timemodified, '%d/%m/%y') AS 'Obtained Date'
FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE gi.courseid = c.id and cc.name = '2017' and gg.rawgrademax=gg.finalgrade
ORDER BY lastname
If anyone could offer a suggestion it would be greatly appreciated! Thanks!