AnsweredAssumed Answered

ADMIN SQL query not working that works on Navicat

Question asked by vs0068169 on Jan 30, 2018
Latest reply on Apr 2, 2018 by vs0068169

I have a query that works on Navicat. I'm trying to locate a specific bit of text (in base 64 encoding below) in a block that is imported into courses as a template. This is so I can workout which courses have been templated.

 

The query below work fine on my dev site..But in the  Admin SQL it returns "The SQL entered is not supported by this report or is incorrect. Error reading from database"

 

I was hoping there are some SQL gurus who might be able to assist?

 

SELECT DISTINCT

mdl_course_categories.id,

mdl_course_categories.`name`,

mdl_block_instances.blockname,

mdl_course.shortname,

mdl_block_instances.configdata,

mdl_block_instances.parentcontextid,

mdl_course.id,

mdl_course.fullname,

mdl_course_categories.parent,

mdl_course.theme

FROM

mdl_context

JOIN mdl_block_instances ON mdl_block_instances.parentcontextid = mdl_context.id

JOIN mdl_course ON mdl_course.id = mdl_context.instanceid

JOIN mdl_course_categories ON mdl_course_categories.id = mdl_course.category

WHERE

mdl_context.contextlevel = 50

AND mdl_block_instances.blockname LIKE 'html'

AND mdl_block_instances.configdata NOT LIKE "Tzo4OiJzdGRDbGFzcyI6Mzp7czo1OiJ0aXRsZSI7czoyMToiQWRtaW4gLSBQbGVhc2UgaWdub3JlIjtzOjY6ImZvcm1hdCI7czoxOiIxIjtzOjQ6InRleHQiO3M6MTAwOiI8ZGl2IGNsYXNzPSJmdWxsc2NyZWVuLWJnIj4NCg0KPGltZyBzcmM9IiIgYWx0PSJCYWNrZ3JvdW5kIGltYWdlIC0gIiBzdHlsZT0id2lkdGg6IDEwMCU7Ij4NCg0KPC9kaXY+Ijt9"

Outcomes