Katie,
Ah, are you using Config reports and/or the Ad-hoc queries plugins?
Those are both read only basically, though Config Reports will let you use some basic things like UPDATE if you turn off the "SQL Security" in its plugin settings. But it can't create things like views or temp tables and so forth. However, if you create views another way, you can certainly use those in your queries, as long as the views are in the same moodle db (because the plugins run as the Moodle user set up for that db and that should be the only one it has access to.).
A common and useful view is one for user firstname and lastname. I usually do one for course completions because its a popular request. Also, if your find yourself having to do reports from the logs, a view can dramatically speed things up, for instance if you have to do a query for "last time a student entered their courses".
If you are new to Moodle then perhaps these things may help:
- Do you have Adminer installed? https://moodle.org/plugins/local_adminer. Everyone has their favorite tools, but this one is very handy because it is right there in Moodle.
- I assume you are aware of the Ad-hoc contributed queries page: https://docs.moodle.org/310/en/ad-hoc_contributed_reports but if not, it's a mismash but I borrow from it all the time.
- Finally, most of the questions about queries are posted over in the forum for the Configurable Reports plugin rather than here, mostly because of history I suppose, but most of the SQL people hang out there: https://moodle.org/mod/forum/view.php?id=7979
Ah, are you using Config reports and/or the Ad-hoc queries plugins?
Those are both read only basically, though Config Reports will let you use some basic things like UPDATE if you turn off the "SQL Security" in its plugin settings. But it can't create things like views or temp tables and so forth. However, if you create views another way, you can certainly use those in your queries, as long as the views are in the same moodle db (because the plugins run as the Moodle user set up for that db and that should be the only one it has access to.).
A common and useful view is one for user firstname and lastname. I usually do one for course completions because its a popular request. Also, if your find yourself having to do reports from the logs, a view can dramatically speed things up, for instance if you have to do a query for "last time a student entered their courses".
If you are new to Moodle then perhaps these things may help:
- Do you have Adminer installed? https://moodle.org/plugins/local_adminer. Everyone has their favorite tools, but this one is very handy because it is right there in Moodle.
- I assume you are aware of the Ad-hoc contributed queries page: https://docs.moodle.org/310/en/ad-hoc_contributed_reports but if not, it's a mismash but I borrow from it all the time.
- Finally, most of the questions about queries are posted over in the forum for the Configurable Reports plugin rather than here, mostly because of history I suppose, but most of the SQL people hang out there: https://moodle.org/mod/forum/view.php?id=7979
Randy