Multiple optional variables with default value

Get Help
1

Hello!

I need to create a column that do some operation with multiple user inputs. If one of the inputs is not provided, I need to get a default value from my table. I need something like:

SELECT
[[{{INPUT_1}} --]] DEFAULT_1 * [[{{INPUT_2}} --]] DEFAULT_2 * [[{{INPUT_3}} --]] DEFAULT_3
FROM MY_TABLE

This works if no input is provided, the content inside all brackets is ignored. But if an input is provided, this query fails because everything after the brackets will be ignored.

I need something "if some input is not provided, we use the default value from the table, if the input is provided, we ignore the default value".

Someone knows how can I do that?

Thanks in advance.

2

Hi @RenanSchwyz
Yes, it's possible, but each optional clause has to be on their own lines.
But the variables are meant to be used in the where-clause, so your usage is outside the scope.
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html

3

Hello @flamber!

It worked, thanks.

I see.. there's a way to do that in the select-clause in a correct way?

4

@RenanSchwyz The variables are only built to be part of the WHERE-clause - they might work elsewhere, but that's up to you to find out.