MariaDB to calculate table reference in select query

Question

I have a quite dumb client application that wants to get information from MariaDB based on a parameter. This parameter is a string that contains spaces, like 'valid parameter'. In MariaDB there is a table for each of the possible string values, and the table name is the string value after spaces have been replaced by underscores and a prefix is added. So I can perform the necessary conversion like this:

SELECT CONCAT('prefix_', REPLACE('valid parameter',' ','_'));

Now the result 'prefix_valid_parameter' is the table to query, so actually I need to fire off

SELECT * from CONCAT('prefix_', REPLACE('valid parameter',' ','_'));

but MariaDB responds with

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '('prefix_', REPLACE('valid parameter',' ','_'))' at line 1

I would have expected either the content of table 'prefix_valid_parameter' or an error stating "table 'prefix_valid_parameter' not found". How can I make the table_reference part of my SQL SELECT statement dynamic?

Gordon Linoff · Accepted Answer · 2020-11-06 12:00:07Z

0

You need to use dynamic SQL:

set @sql = 'select * from [table]';

execute immediate replace(@sql, '[table]',
                          concat('prefix_', replace('valid parameter', ' ', '_'))
                         );

I should add that the need to do this perhaps suggests a flaw in your data model. If the tables have the same structure, it is better to put all the rows in a single table.

answered Nov 6, 2020 at 12:00

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

queeg Over a year ago

The tables have a few common columns, but most of them are special. Hence the decision to have the in separate tables.

queeg Over a year ago

It works in a proper SQL client. But for crying out loud: My dumb client is Grafana, and this one neither can manipulate string values itself (github.com/grafana/grafana/issues/8259) nor can it pass the job to MariaDB using dynamic SQL (github.com/grafana/grafana/issues/8755). I am speechless.

Gordon Linoff Over a year ago

@HiranChaudhuri . . . Perhaps you should ask another question about Grafana and dynamic SQL.

queeg Over a year ago

I already placed my comments on the two relevant github entries - both are referenced in my above comment. But thank you for the hint. :-)

Collectives™ on Stack Overflow

MariaDB to calculate table reference in select query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related