0

My question is the same as this one: Declare a variable in a 'read only' MySQL database, but I cannot use the solution there, as my query is too long and would require too many extra joins that slow down the query and make it time out.

I have read-only access to a MySQL database that I access using MySQL Workbench.

Should I expect to be able to use variables? So far I'm having no luck. I have tried the following:

set @project_ids := '674,705' -- RESTRICT PROJECTS; list must have no spaces
select distinct t.project_id, count(distinct(t.user_id)) as 'Total Users'

from table t

where find_in_set(@project_ids, t.project_id) -- RESTRICT PROJECTS

group by t.project_id

but I just get:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select distinct t.project_id at line 2'

I have also tried:

-- where find_in_set(pm.project_id, @project_ids) <> 0 -- RESTRICT PROJECTS: project_id field is in set of project ids
-- where pm.project_id in (674, 705) -- RESTRICT PROJECTS

I have also tried moving the set @project_ids := '674,705' line to various places in the query, which also fail.

The reason I want to use a variable to restrict the list of project_ids is because the actual query is pretty long (275 lines, joins 20 tables together, includes 5 subqueries plus a union - it's a request from a colleague, and this is the only sticking point) and refers to this list 8 times - I don't want (them) to have to update this query in 8 places every time I/they run it.

tania
  • 1
  • 1
  • Semicolon after the first line? – Mech Jun 09 '20 at 03:39
  • 1
    It ran, hooray! But it produced no output, which it definitely should :( – tania Jun 09 '20 at 03:40
  • double distinct values? – Mech Jun 09 '20 at 03:41
  • 'double distinct values' - do you mean on the SELECT line and in the COUNT? This works just fine in this format: `select distinct t.project_id, count(distinct(t.user_id)) as 'Total Users' from table t where t.project_id in (674, 705) group by t.project_id` – tania Jun 09 '20 at 03:44
  • Was thinking that the error could be that too many distincts would restrict too much of the select process and return an error. In reality, it would more than likely just return very limited results. – Mech Jun 09 '20 at 03:45
  • no luck with the semicolon at the end of the first line? – Mech Jun 09 '20 at 03:46
  • semicolon at the end of the first line made the query run, but not return any results, even though it definitely should return results. I have no idea why. – tania Jun 09 '20 at 03:48
  • so it could be a combination of what I've suggested. Can you add the semicolon and remove the first distinct and see what happens? – Mech Jun 09 '20 at 03:49
  • Added semicolon, changed 'SELECT DISTINCT' to just 'SELECT'. It ran, but gave no output. – tania Jun 09 '20 at 03:51
  • what is your delimiter set as? – Mech Jun 09 '20 at 03:55
  • I believe the delimiter is a semi-colon, but I can't see anywhere where it is set. – tania Jun 09 '20 at 03:58
  • Looks like this could be your issue https://stackoverflow.com/questions/51335920/find-in-set-with-multiple-value – Mech Jun 09 '20 at 04:06
  • Not sure it's a typo in your question or not, but the code you posted in your question is showing find_in_set(@project_ids, t.project_id) that looks backwards to me. I believe your set should be the second parameter. Again maybe it's just a typo. But you may want to double-check. https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_find-in-set – clockwatcher Jun 09 '20 at 04:08
  • *gasp*, my arguments were in the wrong order! Thanks! – tania Jun 09 '20 at 04:10
  • So, the actual solution here was to: 1. add a semicolon to end of SET statement 2. get the arguments of FIND_IN_SET in the right order. Thanks! – tania Jun 09 '20 at 04:10

1 Answers1

0

Solution:

  1. add a semicolon to end of SET statement
  2. get the arguments of FIND_IN_SET in the right order, should be (field-to-look-in, @variable-that-contains-values-to-look-up)
tania
  • 1
  • 1