Default Value in Query


#1

Hi,
The documentation states you can define default parameters within your query, and lists the following example:

SELECT p.*
FROM products p
WHERE p.createdAt = [[ #]]CURRENT_DATE()

I don’t understand how to get this to work for my situation. I want the user to be able to enter the tradeweek they want data for, alternatively the default tradeweek is selected via a simple query:

SELECT tradeweek_code FROM tradeweek WHERE getDate() BETWEEN from_date AND to_date)

I don’t know how to add the above logic into my query below:

SELECT department, category, tradeweek
FROM loyalty_sales
WHERE tradeweek = {{tradeweek_code}}

Any suggestions?


#2

If SELECT tradeweek_code FROM tradeweek WHERE getDate() BETWEEN from_date AND to_date returns the value you want, then I think you can just do:

SELECT department, category, tradeweek
FROM loyalty_sales
WHERE tradeweek = [[ {{tradeweek_code}} # ]] SELECT tradeweek_code FROM tradeweek WHERE getDate()
BETWEEN from_date AND to_date

Notice you’re defining the custom case with the filter in the square brackets, and including the # to comment out the code that remains on that line. If the filter is null, then Metabase will omit everything in square brackets, leaving you with the SELECT statement to be evaluated, instead.


#3

Thanks @dodongo.

That works correctly if you don’t enter a value into the parameter box, although if a user defined value is entered it throws an error: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ‘#’.


#4

Maybe try a – ]] rather than # ]] ? I’ll confess it’s been a few years since doing anything with SQL Server, so perhaps my syntax is off. (to be clear, that’s two hyphens -, not an en-dash).


#5

Thanks. That worked. Great idea, that didn’t even cross my mind.


#6

Fantastic! Glad to hear. Have fun! :slight_smile: