I am using the Variable for Table to change to a different table (the design is the same in both tables)
When using a normal query it works well, and replace the FROM and JOIN names, but not in complex query. The idea is to replace the mdl in table name with mdlbu.
When there are select statements with in the main query it does not work. see CASE statement.
eg:
SELECT
u.firstname as ‘Name’,
u.lastname as ‘Surname’,
c.fullname as ‘Course’,
m.name as ‘Module’,
CASE
WHEN m.name = ‘assign’ THEN (SELECT name FROM mdl_assign WHERE id = cm.instance)
WHEN m.name = ‘book’ THEN (SELECT name FROM mdl_book WHERE id = cm.instance)
END AS Activityname,
FROM mdl_role_assignments AS ra
JOIN mdl_context AS ctx ON ctx.id = ra.contextid AND ctx.contextlevel = 50
JOIN mdl_course AS c ON c.id = ctx.instanceid
JOIN mdl_user AS u ON u.id = ra.userid
JOIN mdl_course_completions AS cc ON cc.course = c.id AND cc.userid = u.id
JOIN mdl_course_modules AS cm ON cm.course = c.id
JOIN mdl_modules AS m ON m.id = cm.module
left JOIN mdl_course_modules_completion as cmc on cmc.coursemoduleid=cm.id and cmc.userid = u.id
This MAY be related to a similar problem to one I have with the save grid functionality on (not very) complex queries. Save Grid not working (Giving a modal error on 'apply') - #8 by ibatey
In my case, the initial WHERE clause is truncated if it is longer than around 30 characters. this gives unpredictable (but wrong) results and errors. It might be worth checking that NM isn’t doing something similar in your case. I used debug mode and a LOT of faffing about to establish my root cause.
Dear @Nico,
Is an error returned or does a crash occur, something like that?
Let us know that detail so we can stay aware.
Best regards!
@Danilo_Lima There no crash or error.
The wrong data is returned. See the select statement in the Case section. I turn on debug and run the test as follows:
When there is a case statement and it has a select, then none of the table names are replaced.
If I remove the the Case section then the Table names in the bottom are replaced.
THanks for your feedback, @Nico
The problem was identified in the tests we carried out and has already been reported to the responsible team.
As soon as the fix is released, we will be providing feedback through this thread.
Best regards!