select *
from status_amount_cms
where merchant_name = '{{merchant}}'
I put it like that but I have an error
@louisc Use Optional Clauses: https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#optional-clauses
select *
from status_amount_cms
where
[[merchant_name = '{{merchant}}' ]]
I did this but still got an error
'The column index is out of range: 7, number of columns: 6.'
@louisc Because everything within the optional clauses will not be created, which means you just have an empty WHERE-clause, which is invalid syntax.
Either:
[[where merchant_name = '{{merchant}}' ]]
Or:
where true
[[and merchant_name = '{{merchant}}' ]]
select *
from status_amount_cms
[[where merchant_name = '{{merchant}}' ]]
I did like that and when I dont input anything in the filter it outputs the default which is good but when i want to filter it has again an error.
'The column index is out of range: 7, number of columns: 6.'
@louisc You should not enclose variables with quotes, Metabase does this. Please read the entire documentation: https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html
select *
from status_amount_cms
where true
[[merchant_name = {{merchant}} ]]
yes sorry I had forgotten to take them out but still have an error when i input in the filter.
ERROR: syntax error at or near "merchant_name" Position: 4336
Here is my full code if it helps
with desired_cohort as (
select
id
from orders_order
where 1=1
and is_order_authorized = 'true'
and is_order_using_elyn = 'true'
and order_date >={{start_range_date}}
and order_date <={{end_range_date}}::date + interval '1 day'
)
, status_desired_cohort as (
select distinct
order_id
, status
, created_at as start_date_status
, coalesce(lead(created_at,1)OVER (PARTITION BY order_id ORDER BY created_at), current_date) as end_date_status
from orders_orderstatus
join desired_cohort on desired_cohort.id=orders_orderstatus.order_id)
,status_desired_cohort_status as (
select *
from status_desired_cohort
where 1=1
and start_date_status <={{selected_date}}::date + interval '22 hours'
and end_date_status >={{selected_date}}
)
,status_desired_cohort_status_filter as (
select *
from (
select order_id,
status,
start_date_status,
end_date_status,
max(start_date_status) over (partition by order_id) as max_status
from status_desired_cohort_status
) t
where start_date_status = max_status)
,status_desired_cohort_status_filter_tenant as (
SELECT distinct
status_desired_cohort_status_filter.*,
orders_orderstatus.tenant_id
FROM
status_desired_cohort_status_filter
inner JOIN
orders_orderstatus ON status_desired_cohort_status_filter.order_id = orders_orderstatus.order_id
)
,status_desired_cohort_status_filter_tenant_name as (
SELECT distinct
status_desired_cohort_status_filter_tenant.*,
tenants_tenant.merchant_name
FROM
status_desired_cohort_status_filter_tenant
inner JOIN
tenants_tenant ON status_desired_cohort_status_filter_tenant.tenant_id = tenants_tenant.id
)
,status_desired_cohort_status_filter_tenant_name_total as (
SELECT distinct
status_desired_cohort_status_filter_tenant_name.*,
orders_orderamount.amount_total_cart,
orders_orderamount.created_at,
orders_orderamount.updated_at
FROM
status_desired_cohort_status_filter_tenant_name
inner JOIN
orders_orderamount ON status_desired_cohort_status_filter_tenant_name.order_id = orders_orderamount.order_id
)
,status_desired_cohort_status_time as (
select distinct status_desired_cohort_status_filter_tenant_name_total.*,
status_desired_cohort_status_filter_tenant_name_total.created_at as start_date_status_cart
, coalesce(lead(status_desired_cohort_status_filter_tenant_name_total.created_at,1)OVER (PARTITION BY order_id ORDER BY status_desired_cohort_status_filter_tenant_name_total.created_at), current_date) as end_date_status_cart
from status_desired_cohort_status_filter_tenant_name_total
)
,status_desired_cohort_status_vrai as (
select *
from status_desired_cohort_status_time
where 1=1
and start_date_status_cart <={{selected_date}}::date + interval '1 day'
and end_date_status_cart >={{selected_date}}
)
,orders_orderamount_total_cart as (
select *
from (
select *,
max(start_date_status_cart) over (partition by order_id) as max_date
from status_desired_cohort_status_vrai
) t
where start_date_status_cart = max_date)
,status_with_max_captured as (
SELECT distinct
orders_orderamount_total_cart.*,
orders_orderamount.amount_captured,
orders_orderamount.amount_previously_captured,
orders_orderamount.id
FROM
orders_orderamount_total_cart
inner JOIN
orders_orderamount ON orders_orderamount_total_cart.order_id = orders_orderamount.order_id
)
,status_with_max_captured_amount as (
SELECT
status_with_max_captured.*,
last_value(status_with_max_captured.amount_captured)
over (
partition by order_id
)
FROM
status_with_max_captured
)
,max__amount_id as (
select *
from (
select *,
max(id) over (partition by order_id) as max_order_amount_id
from status_with_max_captured_amount
) t
where id = max_order_amount_id)
,max__amount_id_addition as (
select *,
amount_captured + amount_previously_captured as total_amount_captured
from max__amount_id
)
,status_amount_cms as (
SELECT distinct
max__amount_id_addition.*,
orders_order.initial_amount_total_pulled_from_cms
FROM
max__amount_id_addition
inner JOIN
orders_order ON max__amount_id_addition.order_id = orders_order.id
)
,merchant_filter as (
select *
from status_amount_cms
where true
[[merchant_name = {{merchant}} ]]
)
select *
from merchant_filter
where (status = 'closed'or status = 'closed_by_admin')
@louisc Here's the trick. Replace {{variable}} with whatever it is supposed to represent (eg. 'text') and remove the optional clauses. If the query works, then you know it's correct.
Please read:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html
https://www.metabase.com/learn/sql-questions/sql-variables.html
https://www.metabase.com/learn/sql-questions/field-filters.html
They will teach you a lot about how variables works.
1 Like
i get this error: "ERROR: syntax error at or near "merchant_name" Position: 4336"
when i run my code.
select *
from status_amount_cms
where true
[[ merchant_name = {{merchant}} ]]
It works when i dont input anything the filter works as default but when i select a merchant i have the error
Is where true merchant_name = 'some value' valid SQL? You are missing AND
where true
[[ AND merchant_name = {{merchant}} ]]
It works perfect when i input directly a text( so a merchant name directly) so the problem comes from the variable.
I have done
select *
from status_amount_cms
where true
[[ AND merchant_name = {{merchant}} ]]
and when I select the variable merchant name it brings up an error :
ERROR: syntax error at or near "=" Position: 4399
@louisc I have absolutely no idea which variable type you are using, so post a screenshot showing that.
@louisc Pretty please; read all the articles I've linked several times previously: Having a dashboard filter that is not obliged to be used
@flamber thanks for your help and I have tried as a text filter and works fine but only for field filters. I have tried everything else but still an error message.
select *
from status_amount_cms
where true
[[ AND merchant_name = {{merchant}} ]]
i get: ERROR: syntax error at or near "=" Position: 4399
@louisc You are not reading the pages:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#field-filter-syntax
https://www.metabase.com/learn/sql-questions/field-filters.html#omit-the-direct-assignment-in-the-where-clause
Field Filter are not using the same syntax as simple variables.
[[ AND {{merchant}} ]]
1 Like
@louisc
If you are using Field Filters then:
- you cannot use table aliases
- you cannot use the variable in other places than the WHERE-clause
- you should not use column identifier and operator, since that is controlled by Metabase
If you cannot make Field Filters work for you, then don't use them. If you want to use them, then read the articles I have linked to many times - and read them one more time.
1 Like