Hi, I am new to metabase.
Database=Postgres 9.5.3
Metabase=v0.24.1
My query is:
SELECT
d.name AS Dept,
aoc.name AS AreaOfConcern,
s.name AS Standard,
me.name AS MeasurableElement,
c.name AS Checkpoint,
cs.score AS Score,
cs.remarks AS Remarks
FROM checkpoint_score cs
INNER JOIN checkpoint c ON cs.checkpoint_id = c.id
LEFT OUTER JOIN measurable_element me ON me.id = c.measurable_element_id
LEFT OUTER JOIN standard s ON s.id = me.standard_id
LEFT OUTER JOIN area_of_concern aoc ON aoc.id = s.area_of_concern_id
LEFT OUTER JOIN checklist cl ON cl.id = cs.checklist_id
LEFT OUTER JOIN department d ON d.id = cl.department_id
LEFT OUTER JOIN facility_assessment fa ON cs.facility_assessment_id = fa.id
LEFT OUTER JOIN facility f ON f.id = fa.facility_id
WHERE f.name = {{facility_name}}
[[AND d.name = {{department_name}}]]
[[AND aoc.name = {{aoc_name}}]]
[[AND s.name = {{standard_name}}]]
[[AND cs.score >= {{min_score}}]]
[[AND cs.score <= {{max_score}}]]
ORDER BY cl.name, aoc.reference, s.reference, me.reference;
I get the error:
ERROR: syntax error at or near “=” Position: 884
As far as I can tell by running the query below in the psql it works fine.
In log file this is error:
{:query
“SELECT\n d.name AS Dept,\n aoc.name AS AreaOfConcern,\n s.name AS Standard,\n me.name AS MeasurableElement,\n c.name AS Checkpoint,\n cs.score AS Score,\n cs.remarks AS Remarks\nFROM checkpoint_score cs\n INNER JOIN checkpoint c ON cs.checkpoint_id = c.id\n LEFT OUTER JOIN measurable_element me ON me.id = c.measurable_element_id\n LEFT OUTER JOIN standard s ON s.id = me.standard_id\n LEFT OUTER JOIN area_of_concern aoc ON aoc.id = s.area_of_concern_id\n LEFT OUTER JOIN checklist cl ON cl.id = cs.checklist_id\n LEFT OUTER JOIN department d ON d.id = cl.department_id\n LEFT OUTER JOIN facility_assessment fa ON cs.facility_assessment_id = fa.id\n LEFT OUTER JOIN facility f ON f.id = fa.facility_id \nWHERE f.name = {{facility_name}}\n[[AND d.name = {{department_name}}]]\n[[AND aoc.name = {{aoc_name}}]]\n[[AND s.name = {{standard_name}}]]\n[[AND cs.score >= {{min_score}}]]\n[[AND cs.score <= {{max_score}}]]\nORDER BY cl.name, aoc.reference, s.reference, me.reference;”,
:template_tags
{:aoc_name {:id “43894d28-d5b6-916d-7cf4-47d944893bd4”, :name “aoc_name”, :display_name “Area Of Concern Name”, :type “dimension”, :dimension [“field-id” 246], :widget_type “category”},
:standard_name {:id “a541bce0-9160-09c7-2f14-2fbca4d57b65”, :name “standard_name”, :display_name “Standard”, :type “dimension”, :dimension [“field-id” 380], :widget_type “category”},
:min_score {:id “6623e710-64bc-602e-293e-2cdded2c3ac1”, :name “min_score”, :display_name “Min Score”, :type “number”},
:max_score {:id “b7b91533-34f2-def3-483d-41a40713367d”, :name “max_score”, :display_name “Max Score”, :type “number”},
:department_name {:id “eb8a4c6f-a245-ac0b-6373-247db40efcdb”, :name “department_name”, :display_name “Department name”, :type “dimension”, :dimension [“field-id” 316], :widget_type “category”},
:facility_name {:id “2fc4cf83-4d17-d641-ef44-05521384d204”, :name “facility_name”, :display_name “Facility name”, :type “dimension”, :dimension [“field-id” 331], :widget_type “category”}},
:collection “area_of_concern”},
:parameters [{:type “category”, :target [“dimension” [“template-tag” “facility_name”]], :value “CHC Lormi”}],
:constraints {:max-results 10000, :max-results-bare-rows 2000},
:info
{:executed-by 1,
:context :ad-hoc,
:query-hash [-10, -123, 68, 54, 20, -68, 48, -77, -119, -128, 46, -83, -96, 101, 115, -127, 41, -70, 71, -109, 17, -109, 35, -98, -61, 123, 63, -128, -97, -105, -12, 59],
:query-type “native”}},
:expanded-query nil}
Jul 06 01:13:18 WARN metabase.query-processor :: Query failure: ERROR: syntax error at or near “=”
Position: 884
[“query_processor$assert_query_status_successful.invokeStatic(query_processor.clj:159)”
“query_processor$assert_query_status_successful.invoke(query_processor.clj:152)”
“query_processor$run_and_save_query_BANG_.invokeStatic(query_processor.clj:191)”
“query_processor$run_and_save_query_BANG_.invoke(query_processor.clj:185)”
“query_processor$fn__26961$dataset_query__26966$fn__26967.invoke(query_processor.clj:223)”
“query_processor$fn__26961$dataset_query__26966.invoke(query_processor.clj:209)”
“api.dataset$fn__28474$fn__28477.invoke(dataset.clj:39)”
“api.common.internal$do_with_caught_api_exceptions.invokeStatic(internal.clj:229)”
“api.common.internal$do_with_caught_api_exceptions.invoke(internal.clj:224)”
“api.dataset$fn__28474.invokeStatic(dataset.clj:33)”
“api.dataset$fn__28474.invoke(dataset.clj:33)”
“middleware$enforce_authentication$fn__38784.invoke(middleware.clj:120)”
“api.routes$fn__38910.invokeStatic(routes.clj:59)”
“api.routes$fn__38910.invoke(routes.clj:59)”
“routes$fn__39540$fn__39541.doInvoke(routes.clj:64)”
“routes$fn__39540.invokeStatic(routes.clj:60)”
“routes$fn__39540.invoke(routes.clj:60)”
“middleware$log_api_call$fn__38883$fn__38885.invoke(middleware.clj:329)”
“middleware$log_api_call$fn__38883.invoke(middleware.clj:328)”
“middleware$add_security_headers$fn__38833.invoke(middleware.clj:243)”
“middleware$bind_current_user$fn__38788.invoke(middleware.clj:140)”
“middleware$maybe_set_site_url$fn__38837.invoke(middleware.clj:266)”]