Dynamic table name in query

Queries and Resources
1

Hello,
I need to update the name of a table based on a {{select1.value}}
but it doesn't work. I get this error "dataset:table_ ? does not match any table."

My query looks like
SELECT * FROM project.dataset.table_{{select1.value}}

Can you please help?
Thanks,
AK

2

Hey @Amira and welcome to the community! Can you try putting all of the table name in curly braces? So {{ project.dataset.table_ + select1.value }}

3

Stumbled upon a similar issue. In my case it was much simpler with no prefix.
select * from {{ table_name.value }} where table_name is a drop down prefilled with all the tables.

The problem is that the resultant query has $1 instead of the selected table and hence the query becomes select * from $1 even though $1 evaluates to a valid table name.
Screenshot below :
select-dropdown

4

Ah yes, this is because most SQL databases use prepared statements. If you disable them in your resource settings, you should be good to go.

2 Likes
5

Yes, one of the error messages actually prompted it. Was just a little apprehensive because the option has a note that it turns off SQL injection protection. Just curious if there are any other workarounds ?

PS : Maybe a different issue, but I am not able to save that option by editing the resource. The prepared statements checkbox isn’t getting saved.

Cheers !

6

I think I have the same problem. The solution I found is to use CASE as follows

	case
	when {{ search_by_dropdown.value == 'domain' }} then {{ ! search_by.value}}
	or s.domain ilike {{'%' + search_by.value.trim() + '%' }}
	when {{ search_by_dropdown.value == 'email' }} then {{ ! search_by.value}}
	or c.account_email ilike {{'%' + search_by.value + '%' }}
	or u.email ilike {{'%' + search_by.value.trim() + '%' }}
	when {{ search_by_dropdown.value == 'customerName' }} then {{ ! search_by.value}}
	or c.name ilike {{'%' + search_by.value.trim() + '%' }}
when {{ search_by_dropdown.value == 'token' }} then {{ ! search_by.value}}
	or st.token ilike {{'%' + search_by.value.trim() + '%' }}
end

and I am not able to edit the resource either :confused:

1 Like
7

Many thanks for your help :slight_smile:

8

Many thanks :slight_smile:

9

I was able to edit the resource so this solution worked for me :slight_smile:

1 Like
10