Field filter not working when a value is selected

Hi there ! - facing issue with filed filter. When I execute the stored procedure without selecting a value in drop down it seems to work which is also a parameter in the stored procedure, when I select a value in drop down it does not retrieve the data.


Hi @sgayathri
You cannot control the code generated by Field Filters:
https://www.metabase.com/docs/latest/questions/native-editor/sql-parameters.html
You would have do something like

Demo_GetEmployee [[ (select city from monitortest where {{city}} limit 1) ]]

I don't know if that will even work, but otherwise you have to use a Text variable.

Thanks for your response. Now I am able to duplicate this one with even a simple SQL query.

  1. I have two simple tables:
  • Employees (EmpID, EmpName, DepartmentID)
  • Departments (DepartmentID, Name)
  1. I tried the following query,
--  departmentid is a field filter
SELECT * FROM Employees WHERE {{departmentid}} 
  1. Didn't give desired result, so I used Sql Server Profiler to see what query is getting fired, this is what I see:
-- 3 is the Department I selected in the Field dropdown
SELECT * FROM Employees WHERE "dbo"."Departments"."DepartmentID" IN (3)
  1. so I corrected the field filter like below and it is working
SELECT * FROM Employees e 
[[WHERE DepartmentID = (SELECT DepartmentID FROM Departments WHERE {{DepartmentID}})]]

In Sql Profiler, this is what I see:

SELECT * FROM Employees e 
WHERE DepartmentID = (SELECT DepartmentID FROM Departments 
WHERE "dbo"."Departments"."DepartmentID" IN (3))

At this point of time, I'm really confused and wondering what mistake I'm doing as this whole Field Filter does not make any sense under Sql Server context. Appreciate your response.

Thanks!

@sgayathri I don't know what your question is, but looks like you just want to do this, where the Field Filter reference would be for Employees.DepartmentID in the sidebar:

SELECT * FROM Employees WHERE {{DepartmentID}}

This is what we did in the bullet no 2 above, but SQL server errored out since Metabase is resolving this query as
SELECT * FROM Employees WHERE "dbo"."Departments"."DepartmentID" IN (3)
which is not a valid SQL query

@sgayathri That's because you are referencing the wrong table and column in the Variables sidebar.

Thanks for your response. So this works now.

SELECT * FROM Employees WHERE {{DepartmentID}}

But when I try the same for my stored procedure, I get the following in profiler.

-- sproc
EXEC Demo_GetEmployees @DepartmentID = {{DepartmentID}}


--  profiler
EXEC Demo_GetEmployees @DepartmentID = 1 = 1

@sgayathri You have to use a sub-select (if it is even possible)

EXEC Demo_GetEmployees @DepartmentID = (SELECT DepartmentID FROM Employees WHERE {{DepartmentID}})

If that doesn't work, then it's not possible to use Field Filter and you'll have to figure out another solution.