Issue with variables in SQL query

Get Help
1

Hi All,

I’m trying to insert a field filter to the following query so that I can filter by Name. It looks like the variable in this instance does not work on the joined table “salesforce.user d”. I’ve tried multiple options but I can’t make it work. For context, the “Name” field is coming from the “salesforce.user d” table.

Would love to know if anybody has any clue on how to make it work?

SELECT
a.user_id,
b.name AS “Account Name”,
b.company_tier_c AS “Company Tier”,
b.account_mrr_c “Current Account MRR”,
b.last_contract_end_date_c AS “Contract End Date”,
DATEDIFF(days,b.last_contract_end_date_c, CURRENT_DATE) AS “Days Before Contract Ending”,
c.balance/100 as “Available Content Balance”,
b.open_new_opportunities_c,
b.is_account_active_c
FROM salesforce.account_team_member a
LEFT JOIN salesforce.account b
ON a.account_id=b.id
LEFT JOIN contently_public.brand_profiles c
ON b.publication_id_1_c=c.id
LEFT JOIN salesforce.user d
ON a.user_id=d.id
WHERE {{name}}
AND b.type = ‘Brand / Division’
AND a.team_member_role = ‘Account Manager’

Looking forward to your help,

Florian

2

If you’re using a field filter you can’t use a table alias.
In my sample data
This works:

SELECT * from customer where  {{test}}

This doesn’t:

SELECT * from customer c where  {{test}}

Either remove the alias or use a variable type of text (but then you’ll lose the list of values).

3

Hi Andrew,

Removing the alias won’t work in this case as there are multiple join. I’ve tried to give the name of the table as Alias but for some reason it still not working as I can’t call the user table as “user”.

SELECT
account_team_member.user_id,
account.name AS “Account Name”,
account.company_tier_c AS “Company Tier”,
account.account_mrr_c “Current Account MRR”,
account.last_contract_end_date_c AS “Contract End Date”,
DATEDIFF(days,account.last_contract_end_date_c, CURRENT_DATE) AS “Days Before Contract Ending”,
brand_profiles.balance/100 as “Available Content Balance”,
account.open_new_opportunities_c,
account.is_account_active_c
FROM salesforce.account_team_member as account_team_member
LEFT JOIN salesforce.account as account
ON account_team_member.account_id=account.id
LEFT JOIN contently_public.brand_profiles as brand_profiles
ON account.publication_id_1_c=brand_profiles.id
LEFT JOIN salesforce.user as user
ON account_team_member.user_id=user.id
WHERE {{name}}

any ideas on how to make this work?

4

@flo92 Why can’t you just use skip the alias for the table you’re using with Field Filter?
LEFT JOIN salesforce.user ON account_team_member.user_id=salesforce.user.id

There’s an issue open on support for table aliases:
https://github.com/metabase/metabase/issues/3324 - upvote by clicking :+1: on the first post

1 Like
5

Thanks, Flamber! looks like it’s working!