CODEWITHSUNDEEP
parametersgoogle-bigquerylooker-studioquery-string
Adriana Cortés Buelvas
Adriana Cortés Buelvas

Reputation: 3

Undeclared query parameters @DS_START_DATE bigquery

So, I have been trying to set parameters @DS_START_DATE and @DS_END_DATE to filter some data in Data Studio. The idea is to compare the year of a transaction with the year of the date selected by the parameter and create a column that saves the value of another one by using a CASE WHEN clause. The query extract goes as follows:

CASE 
WHEN EXTRACT(YEAR FROM fechaTransaccion) = EXTRACT(YEAR FROM CAST(@DS_END_DATE AS DATE))
THEN VlrBruto ELSE 0 END AS VlrBruto_Actual,
CASE WHEN EXTRACT(YEAR FROM fechaTransaccion) =  EXTRACT(YEAR FROM CAST(@DS_START_DATE AS DATE)) 
THEN VlrBruto ELSE 0 END AS VlrBruto_Anterior

I have tried adding a date filter on my data studio report and have already activated date parameters while doing the personalized consult and still is not working. When running the query in bigquery there's a box that says "Undeclared Query Parameters". While running in data studio the prompt says "unexpected consult error".

Any ideas how to solve it? Thanks in advance

Upvotes: 0

Views: 4071

Answers (2)

fuzin
fuzin

Reputation: 166

Try using:

EXTRACT(YEAR FROM PARSE_DATE('%Y%m%d', @DS_END_DATE))

instead of

# EXTRACT(YEAR FROM CAST(@DS_END_DATE AS DATE))

Upvotes: 0

TimVisser
TimVisser

Reputation: 11

If you are using the BQ UI, it will not work, since it does not support parameterized queries.

If you are not using the BQ UI, have you declared and set the variables with the statements DECLARE and SET?

You can also look here for more info: Parameterized queries

Upvotes: 0

Related Questions