CODEWITHSUNDEEP
sqlmariadb
queeg
queeg

Reputation: 9463

MariaDB to calculate table reference in select query

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?

Upvotes: 0

Views: 146

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

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.

Upvotes: 0

Related Questions