Hi, I’ve this query:
SELECT c.business_name,b.lastname, b.firstname,(a.tot_time*b.costo_orario_ordi) as Costo FROM db_summary_syn a
LEFT join users b ON a.codfis=b.codfis
LEFT join db_companies c ON a.idcompany=c.id
WHERE b.onoff=1
[[ AND {{v_lastname}}]]
[[ AND {{v_firstname}}]]
[[ AND a.dataora={{v_dataora}}]]
ORDER BY c.business_name,b.lastname, a.dataora
v_lastname and v_firstname are linked to users.firstname and users.lastname.
When I select a value from this two value and run query, I received an error:
Unknown column ‘users.lastname’ in ‘where clause’
So within the variable setting you selected the relevant fields as field filters, right?
have you tried to set text field filters, then the structure should be
AND v_lastname = {{var}}
the filter has to be a text field then.
Just to find out if the error occurs as well?
Another thing to try could be to use only one filter in the query for a start (delete the other two) and have a look if the error then occurs.
SELECT c.business_name,b.lastname, b.firstname,(a.tot_time*b.costo_orario_ordi) as Costo FROM db_summary_syn a
LEFT join users b ON a.codfis=b.codfis
LEFT join db_companies c ON a.idcompany=c.id
WHERE b.onoff=1
[[ AND b.lastname={{v_lastname}}]]
ORDER BY c.business_name,b.lastname, a.dataora
Only one variable (v_lastname) set as text and…IT WORK.
It would seems a problem of the variable type on a joined table…
Hi Antonio,
I don´t think that the joined table should be the issue.
Have you tried using all three variables in this text filter way - does this work?
another way would be to have all three set in your way (with field filters) but without the “optional” brackets.
Does this work?
We will find out how to make it work
Eva
Hi,
With all three variables set as text, it work.
When I set variables as field filter (without brackets):
SELECT c.business_name,b.lastname, b.firstname,(a.tot_time*b.costo_orario_ordi) as Costo FROM db_summary_syn a
LEFT join users b ON a.codfis=b.codfis
LEFT join db_companies c ON a.idcompany=c.id
WHERE b.onoff=1
AND {{v_lastname}}
AND {{v_firstname}}
ORDER BY c.business_name,b.lastname, a.dataora
Hi Antonio,
I recreated your query and you might be right - when I use field filters from joined tables I get the same error message.
That looks like a bug to me.
For a workaround, are you able to make either the users table your main table or use text filters?
Just for documentation, I use metabase v0.25, in case you use a newer version it would be worth mentioning it so that the metabase guys know where to find the bug.
