Optional Variable

I have an optional paramters in my query.

select userId, {{start_date}} from users
where True
[[ and {{start_date]] >= “2018-01-01”]]

For this query to work how can I use a default value for my optional parameter {{start_date}} in select list when its not passed .

I don’t understand your query. If start_date is a parameter then all your query will return is a list of userIds followed by the value of the parameter.
If the start_date parameter is before 2018, it won’t return anything at all.

What should the query be doing?

My query is getting the users how logged in after a specific time. and If i do not pass any value of {{start_date}} variable it should give me the logged in users from the beginning of time. It works fine when the {{start_date}} paramter is passed but since i have made it optional, when i do not pass this parameter the query failes as its expecting the value of {{start_date}} in select list. the where clause is able to handle the absence of optional paramter. Is there something some option to select a default value in select list when the optional parameter has not been provided by the user ??

When the optional parameter is not given it does not work in select list there should be a way to ignore the optional paramter and use a default value in its place if its not supplied .

@babarali You currently cannot use variables in the SELECT clause - only in the WHERE clause.
You have to either use Required and Default value, or use Complex Default Value:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#setting-a-default-value

Oddly, this worked for me using the Sample Dataset:

SELECT CREATED_AT ,ID [[,{{start_date}}]]
FROM "PUBLIC"."PEOPLE"
WHERE 1=1 [[and {{start_date}} >= CREATED_AT]]

Not sure how complex it can become though.

@AndrewMBaines Yeah, I meant Field Filters - most questions are about that. Not sure what the point of having a simple variable like that in the SELECT clause.

1 Like

I think this can work for me. Great thanks (Y)

@flamber - I agree. The query makes no sense at all!

I have a case where i calculate %utilization of my assets and thats dependent on the input filter values.

1 Like