I am trying to set the current date as the default value for a variable as below
WHERE [[ rr.year = date_part('year', {{CALENDAR_2}}::date) AND rr.month = date_part('month', {{CALENDAR_2}}::date) AND rr.as_of_date <= {{CALENDAR_2}}::date --]] = CURRENT_DATE()
But I am getting the below error when I run it. What am I doing wrong?
You'll need to pick a value for 'Calendar 2' before this query can run.
Hi @sreenath.dressler
Post your full query, since it doesn't look correct.
And did you perhaps set the variable as Required?
Yes I did post the variable as required. Below is the major section of the query
SELECT
mrr.rating,
mrr.borrower_id as borrower_id
FROM (
SELECT
rr.rating,
rr.borrower_id,
RANK() OVER (PARTITION BY rr.borrower_id ORDER BY rr.as_of_date DESC) id_rank
FROM risk_ratings rr
WHERE [[ rr.year = date_part('year', {{CALENDAR_2}}::date) AND rr.month = date_part('month', {{CALENDAR_2}}::date) AND rr.as_of_date <= {{CALENDAR_2}}::date --]] = CURRENT_DATE()
) as mrr
WHERE id_rank = 1
@sreenath.dressler So if you set the variable as Required, then it's not possible to run the query before inputting a value into the filter widget.
Also, your optional clause is wrong.
If you don't input anything, then your query basically does this (if it wasn't Required):
WHERE = CURRENT_DATE()
I'm guessing the end should look like this:
... --]] date(rr.as_of_date) = CURRENT_DATE()
@flamber What I would like to do is basically set the all the variable CALENDAR_2 as todays date by default. I am porting this data from chartio and am a bit new to Metabase. How should then structure the query to be able to set the default value of CALENDAR_2 to todays date.
@sreenath.dressler I understand, which is also specified in the docs:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#setting-complex-default-values-in-the-query
Remove the Required (and any defaults you've set), since you're using Complex Default Values:
SELECT
mrr.rating,
mrr.borrower_id as borrower_id
FROM (
SELECT
rr.rating,
rr.borrower_id,
RANK() OVER (PARTITION BY rr.borrower_id ORDER BY rr.as_of_date DESC) id_rank
FROM risk_ratings rr
WHERE [[ rr.year = date_part('year', {{CALENDAR_2}}::date) AND rr.month = date_part('month', {{CALENDAR_2}}::date) AND rr.as_of_date <= {{CALENDAR_2}}::date --]] date(rr.as_of_date) = CURRENT_DATE()
) as mrr
WHERE id_rank = 1
@flamber Thanks, I think I get it. Is there a way I can get the month and year part of the current date too. I also need to pass the values to part of the equation at
rr.year = date_part('year', {{CALENDAR_2}}::date)
And
rr.month = date_part('month', {{CALENDAR_2}}::date)
Would replacing them as below do?
[[ rr.year = date_part('year', {{CALENDAR_2}}::date) --]] rr.year = date_part('year', CURRENT_DATE())
@sreenath.dressler Yes, that should work.
I would probably do something like this:
WHERE 1=1
AND rr.year = date_part('year', coalesce( [[ {{CALENDAR_2}}::date , ]] CURRENT_DATE() ))
AND rr.month = date_part('month', coalesce( [[ {{CALENDAR_2}}::date , ]] CURRENT_DATE() ))
AND rr.as_of_date <= coalesce( [[ {{CALENDAR_2}}::date , ]] CURRENT_DATE() )
@flamber
Still getting the below error
ERROR: syntax error at or near "(" Position: 2571
Not sure what the position means. Sorry if its a very silly question.
Below is the snippet I ran
loan_status as (
SELECT
mr_lsh.new_status,
mr_lsh.effective_date,
mr_lsh.loan_id
FROM (
SELECT lsh.new_status, lsh.effective_date, lsh.loan_id,
RANK() OVER (PARTITION BY lsh.loan_id ORDER BY lsh.effective_date DESC) id_rank
FROM loan_status_histories lsh
WHERE 1=1
AND lsh.effective_date <= coalesce( [[ {{CALENDAR_2}}::date , ]] CURRENT_DATE() )
) as mr_lsh
WHERE id_rank = 1
AND mr_lsh.new_status = 'Existing'
),mr_risk_rating as (
SELECT
mrr.rating,
mrr.borrower_id as borrower_id
FROM (
SELECT
rr.rating,
rr.borrower_id,
RANK() OVER (PARTITION BY rr.borrower_id ORDER BY rr.as_of_date DESC) id_rank
FROM risk_ratings rr
WHERE 1=1
AND rr.year = date_part('year', coalesce( [[ {{CALENDAR_2}}::date , ]] CURRENT_DATE() ))
AND rr.month = date_part('month', coalesce( [[ {{CALENDAR_2}}::date , ]] CURRENT_DATE() ))
AND rr.as_of_date <= coalesce( [[ {{CALENDAR_2}}::date , ]] CURRENT_DATE() )
) as mrr
WHERE id_rank = 1
)
@sreenath.dressler You're using the word "snippet", but Metabase also has something called Snippets, so just making sure that it's not the same thing we're talking about, because Snippets doesn't support variables.
Try replace [[ {{CALENDAR_2}}::date , ]] with '2021-12-01'::date, - just to figure out where the problem comes from - and try running the query directly on your database, which might provide more specific debug information.
@flamber I simply meant the code snippet, nothing specific to Metabase 
I did try running the code again after removing out the custom value and the CUSTOM_DATE() section you had suggested and it ran successfully. Below is the code I ran. I am not able to find any missing '(' that should cause the error, can you let me know what might cause it.
loan_status as (
SELECT
mr_lsh.new_status,
mr_lsh.effective_date,
mr_lsh.loan_id
FROM (
SELECT lsh.new_status, lsh.effective_date, lsh.loan_id,
RANK() OVER (PARTITION BY lsh.loan_id ORDER BY lsh.effective_date DESC) id_rank
FROM loan_status_histories lsh
WHERE 1=1
AND lsh.effective_date <= {{CALENDAR_2}}::date
) as mr_lsh
WHERE id_rank = 1
AND mr_lsh.new_status = 'Existing'
),mr_risk_rating as (
SELECT
mrr.rating,
mrr.borrower_id as borrower_id
FROM (
SELECT
rr.rating,
rr.borrower_id,
RANK() OVER (PARTITION BY rr.borrower_id ORDER BY rr.as_of_date DESC) id_rank
FROM risk_ratings rr
WHERE 1=1
AND rr.year = date_part('year', {{CALENDAR_2}}::date)
AND rr.month = date_part('month', {{CALENDAR_2}}::date)
AND rr.as_of_date <= {{CALENDAR_2}}::date
) as mrr
WHERE id_rank = 1
)
@sreenath.dressler
No, go back and only
replace [[ {{CALENDAR_2}}::date , ]]
with '2021-12-01'::date,
The point is to debug - that's how you do it, by replace anything Metabase-variable-related with a static value, so you can test your query anywhere to make sure it works as expected.
You'll have to do the legwork here. There are other tips, so read through the documentation too https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html
@flamber, there seems to be a confusion on what I said. Let me rephrase, the code is running correctly as long as I am not using the CURRENT_DATE() portion. I tried again with the method you mentioned too and it ran fine, which leads me to believe that the CURRENT_DATE() syntax has to do something with it.
I also did go through the documentation before raising the discussion here. The document only has a single example with a short description of how to use a complex default value, which is also different from what you suggested. The document in the example uses a # which your example has not. Is there a more descriptive example for the complex default value which a beginner can follow to understand?
@sreenath.dressler Okay, I have no idea which database type you're using, so you'll need to figure out which functions your database has.
I am using the postgres database
@sreenath.dressler Every database has slight quirks, so Postgres is not functions, but constants, so you'll have to use current_date (without parentheses).
My example is actually just Optional Clauses used in a creative way.
The Complex Default Value takes advantage of the comment syntax (also different between databases), which allows you to create more advanced things, but can be difficult to maintain.