I’m not sure what I’m doing wrong with using my template variable in a query. I’m using a postgres database with a table called “enrollment” that contains some enrollments data. I would like to use a template variable to select the enrollments data for on a dropdown using filters for year and insurance type .
but am having this error when i run the query …
: db query error: pq: syntax error at or near “$”
Here is the query i used :
select
short_name,
i.hios_issuer_id,
i.state_of_domicile “State”,
count (ee.id)
from
issuers i
left join enrollment en on
en.hios_issuer_id = i.hios_issuer_id
join enrollee ee on
ee.enrollment_id = en.id
and ee.person_type_lkp in (168, 169)
and en.enrollment_status_lkp <> 1775
and EXTRACT(YEAR FROM benefit_effective_date) IN (${benefit_effective_date})
AND ((‘Health’ IN (${insurance_type_lkp}) AND insurance_type_lkp IN (131)) OR
(‘Dental’ IN (${insurance_type_lkp}) AND insurance_type_lkp IN (132)))
group by
1,
2,
3
order by
i.short_name;
select
short_name,
i.hios_issuer_id,
i.state_of_domicile “State”,
count (ee.id)
from
issuers i
left join enrollment en on
en.hios_issuer_id = i.hios_issuer_id
join enrollee ee on
ee.enrollment_id = en.id
and ee.person_type_lkp in (168, 169)
and en.enrollment_status_lkp <> 1775
and EXTRACT(YEAR FROM benefit_effective_date) IN (‘2024’)
AND ((‘Health’ IN (${insurance_type_lkp}) AND insurance_type_lkp IN (131)) OR
(‘Dental’ IN (${insurance_type_lkp}) AND insurance_type_lkp IN (132)))
group by
1,
2,
3
order by
i.short_name;
sure i just did , but still having same error as before db query error: pq: syntax error at or near “$”
data types should be date which in my case just want the year and string
select
short_name,
i.hios_issuer_id,
i.state_of_domicile “State”,
count (ee.id)
from
issuers i
left join enrollment en on
en.hios_issuer_id = i.hios_issuer_id
join enrollee ee on
ee.enrollment_id = en.id
and ee.person_type_lkp in (168, 169)
and en.enrollment_status_lkp <> 1775
and EXTRACT(YEAR FROM benefit_effective_date) IN ${benefit_effective_date}
AND ((‘Health’ IN ${insurance_type_lkp} AND insurance_type_lkp IN (131)) OR
(‘Dental’ IN ${insurance_type_lkp} AND insurance_type_lkp IN (132)))
group by
1,
2,
3
order by
i.short_name;
EXTRACT(YEAR FROM benefit_effective_date) IN ${benefit_effective_date}
AND ((‘Health’ IN ${insurance_type_lkp} AND insurance_type_lkp IN (131)) OR
(‘Dental’ IN ${insurance_type_lkp}
try to fix those. I would go the stored procedure approach for this. I never use inline sql query.
The weird thing is i used similar approach with another dashboard but with 6 other filers last 3 months and it was working good but now doing same it throws this errors and when i export the old dashboard and make edits on the inline query using the new query it works without errors but when i create a new dashboard it wont work
and EXTRACT(YEAR FROM benefit_effective_date) IN ${benefit_effective_date}
AND ((‘Health’ IN ${insurance_type_lkp} AND insurance_type_lkp IN (131)) OR
(‘Dental’ IN ${insurance_type_lkp} AND insurance_type_lkp IN (132)))
never tried that before … by stored route you mean using the builder ?
as for that parenthesis i did take them out for this but still having same issues … like i said before i have another dashboard i created using same configurations and its working but now am creating a new dashboard its not working … thats why am really confused
