Drop Down filter for sql query for category type

Mohan1155 · January 23, 2020, 10:24am

Hello,
I want to have a drop-down for a filter with some suggestions. I am able to achieve this using a custom query, but I need this for an SQL query.

Select a.SYSTEM_NAME,count(distinct a.LINE_ITEM_ID) as Value from USP_MONITORING.USP_TEST_AUTMN_IGT_USP_COMPARE a
where a.JOB_ID=(select JOB_ID from
(select * from USP_MONITORING.USP_TEST_AUTMN_IGT_USP_COMPARE where NASP_ID = {{NASP_ID}}
ORDER BY CREATED_TIME desc) where rownum=1) group by a.NASP_ID,a.SYSTEM_NAME order by SYSTEM_NAME desc

This is the query.

maz · January 23, 2020, 5:28pm

Check out the documentation on SQL field filter variables: https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html

The main difference is that you’d need to change your WHERE clause to be like where {{nasp_variable}}, and in the variables panel, select the “Field Filter” variable type, and then map that variable to the NASP_ID column, assuming that’s the column for which you’d like to supply a dropdown select.

Mohan1155 · January 24, 2020, 7:24am

Thanks for a quick reply,
Upon doing that, I am getting an error.