Hello,
I’m having issues with a variable in a native SQL. I tried to trouble shoot this in many ways but in vain.
I have a variable which is {{active_contracts_only}} which is setup as a boolean variable.
I use that variable in a few WHERE clauses in CTEs.
The error I get is
My SQL where clause is
WHERE
(
-- CASE 1: Filter is set to TRUE
-- This is now a valid boolean = boolean comparison
{{active_contracts_only}} = true
AND oc.is_active = true
AND kpi.active_date >= oc.contract_start_date
AND kpi.active_date <= oc.contract_end_date
)
OR
(
-- CASE 2: Filter is set to FALSE
-- This is also a valid boolean = boolean comparison
{{active_contracts_only}} = false
[[AND {{period}}]] -- The period filter remains optional
)
I tried another version where I cast the boolean to text. I also get an error about the type.
WHERE 1=1
[[ AND (
(
-- CASE 1: Filter is set to 'true'
-- This casts the variable (which can be boolean true, or string 'true')
-- to text, and compares it to the text string 'true'.
CAST({{active_contracts_only}} AS text) = 'true'
AND oc.is_active = true
AND kpi.active_date >= oc.contract_start_date
AND kpi.active_date <= oc.contract_end_date
)
OR
(
-- CASE 2: Filter is 'false' or EMPTY/NULL
-- This casts the variable (which can be boolean false, string '', or NULL)
-- to text, coalesces NULL to 'false', and checks if it's 'false' or ''
COALESCE(CAST({{active_contracts_only}} AS text), 'false') IN ('false', '')
[[AND {{period}}]] -- Optional period filter
)
)
]]
I tried an even more simple version, I sill get the error: argument of WHERE must be type boolean, not type character
WHERE
oc.is_active = {{active_contracts_only}}
AND kpi.active_date >= oc.contract_start_date
AND kpi.active_date <= oc.contract_end_date
By browser is Vivaldi. I did the following steps:
- Cleared are cookies relating to Metabase. No change
- Tried in a Private window. No change.
Any clue?
Shouldn’t you be comparing a column to the value of your filter?
If your SQL were valid, you’d return nothing if the filter value was false.
Hello @AndrewMBaines
Yes, I am comparing a field value to the filter in the last versions
WHERE
oc.is_active = {{active_contracts_only}}
AND kpi.active_date >= oc.contract_start_date
AND kpi.active_date <= oc.contract_end_date
oc.is_active is a boolean field on a table called organizations_contracts.
under the hood, this field is dynamics. It is made with a DBT model with the lines
case
when now() between mp.valid_from and mp.valid_to then true else false
end as is_active,
But I don’t think thins should be a problem. We can clearly see that the type returned in this line is a boolean.
What version of Metabase are you running?
What is the definition of the {{period}} variable? That will generate that error if it doesn’t replace with an expression or boolean, i.e., it’s a regular filter and not a field-linked filter.
Hello @dwhitemv
We are on v.056.12 (self hosted).
Thanks for the suggestions. It seems you were right, something finicky was going on with the {{Period}} variable even though it was connected to a field date (see screenshot below)
All along, for hours I believe the {{active_contracts_only}} was the problem. I discovered the {{Period}} variable was the problem by removing it fully an running the query.
Its a challenge, as I’m trying to have a complex business logic. If the users are using the active_contract boolean, the dates are defined by the contract dates, if not they are defined by the regular date filter.
The fixed version that runs now is but I believe I still need to tweak it.
[[WHERE
(
-- CASE 1: Filter is set to TRUE
{{active_contracts_only}} = true
AND oc.is_active = true
AND kpi.active_day >= oc.contract_start_date
AND kpi.active_day <= oc.contract_end_date
)
OR
(
-- CASE 2: Filter is set to FALSE
{{active_contracts_only}} = false
)
]] [[AND {{Date}}]]
I changed then {{Period}} variable to {{Date}} and included in its own
Good to hear you found the solution.
One problem I noticed in your fragment there. As written, you need to set active_contracts_only as a required field with a default value. Otherwise if its blank and Date is set then you’ll get a syntax error as the WHERE clause will be removed. If you need to allow active_contracts_only to be non-present or empty, use the WHERE TRUE AND trick so there is a valid predicate if either or both variables are empty.
WHERE TRUE
[[AND ((Case 1) OR (Case 2))]]
[[AND {{Date}}]]
1 Like
