5

I've got an issue similiar to the one described here : Pruning BigQuery partitions with Data studio

I want to declare a variable to run a custom query in data studio.

For example :

DECLARE usd_to_eur_rate FLOAT64;
SET usd_to_eur_rate = 0.8;   

SELECT itemPrice_USD*usd_to_eur_rate as itemPrice_EUR
FROM `dataset.table`

Query works in BigQuery but not in data studio.

As in the above mentioned topic, I get error message :

Data Studio cannot connect to your data set.

Failed to fetch data from the underlying data set

I've read the comment solving the issue :

DECLARE statement don't work in DataStudio, 
because the whole custom query is wrapped in an outer select 
so this syntax becomes invalid SELECT ........ FROM (DECLARE usd_to_eur_rate ..............................)

Still, I haven't figured out a workaround ...

Do you guys have an idea of an alternative syntax that could work into data studio custom query ?

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
JCDB
  • 135
  • 8

2 Answers2

5

@Pentium10, thanks for precision.

I'll post an answer here with how I used this advice to finally get the query work in data studio :

Instead of

DECLARE usd_to_eur_rate FLOAT64;
SET usd_to_eur_rate = 0.8;   

SELECT itemPrice_USD * usd_to_eur_rate AS itemPrice_EUR
FROM `dataset.table`

I've been using :

WITH vars AS
(
    SELECT 0.8 AS usd_to_eur_rate 
)

SELECT itemPrice_USD * (SELECT usd_to_eur_rate FROM vars) AS itemPrice_EUR
FROM `dataset.table`
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
JCDB
  • 135
  • 8
3

A workaround is to create a table to hold that value, and select the value from the table.

As I wrote on the other thread all DataStudio queries bust be wrappable in outer select statement.

Pentium10
  • 204,586
  • 122
  • 423
  • 502