SQL is quite simple:
select count(seller_id), from_unixtime(seller_rd, ‘%Y-%m-%d’) as seller_date
from apt_seller_view_powerbi
where seller_rd >= UNIX_TIMESTAMP(’{{date_from}} 00:00:00’) and seller_rd < UNIX_TIMESTAMP(’{{date_to}} 23:59:59’)
group by seller_date
It returns me an error:
“java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).”
I set “Date” as variable type for both date_from and date_to.
Here’s info from metabase logs:
May 15 10:23:35 WARN metabase.query-processor :: {:status :failed,
:class java.util.concurrent.ExecutionException,
:error “java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).”,
:stacktrace
[“driver.generic_sql.query_processor$cancellable_run_query.invokeStatic(query_processor.clj:468)”
“driver.generic_sql.query_processor$cancellable_run_query.invoke(query_processor.clj:460)”
“driver.generic_sql.query_processor$run_query.invokeStatic(query_processor.clj:485)”
“driver.generic_sql.query_processor$run_query.invoke(query_processor.clj:480)”
“driver.generic_sql.query_processor$do_with_auto_commit_disabled.invokeStatic(query_processor.clj:518)”
“driver.generic_sql.query_processor$do_with_auto_commit_disabled.invoke(query_processor.clj:507)”
“driver.generic_sql.query_processor$do_in_transaction$fn__51371.invoke(query_processor.clj:523)”
“driver.generic_sql.query_processor$do_in_transaction.invokeStatic(query_processor.clj:522)”
“driver.generic_sql.query_processor$do_in_transaction.invoke(query_processor.clj:521)”
“driver.generic_sql.query_processor$run_query_without_timezone.invokeStatic(query_processor.clj:536)”
“driver.generic_sql.query_processor$run_query_without_timezone.invoke(query_processor.clj:535)”
“driver.generic_sql.query_processor$execute_query$fn__51390.invoke(query_processor.clj:558)”
“driver.generic_sql.query_processor$do_with_try_catch.invokeStatic(query_processor.clj:502)”
“driver.generic_sql.query_processor$do_with_try_catch.invoke(query_processor.clj:497)”
“driver.generic_sql.query_processor$execute_query.invokeStatic(query_processor.clj:555)”
“driver.generic_sql.query_processor$execute_query.invoke(query_processor.clj:551)”
“driver$fn__25845$G__25652__25852.invoke(driver.clj:61)”
“query_processor$execute_query.invokeStatic(query_processor.clj:51)”
“query_processor$execute_query.invoke(query_processor.clj:45)”
“query_processor.middleware.mbql_to_native$mbql__GT_native$fn__30118.invoke(mbql_to_native.clj:30)”
“query_processor.middleware.annotate_and_sort$annotate_and_sort$fn__28510.invoke(annotate_and_sort.clj:41)”
“query_processor.middleware.limit$limit$fn__30073.invoke(limit.clj:15)”
“query_processor.middleware.cumulative_aggregations$cumulative_aggregation$fn__29929.invoke(cumulative_aggregations.clj:58)”
“query_processor.middleware.cumulative_aggregations$cumulative_aggregation$fn__29929.invoke(cumulative_aggregations.clj:58)”
“query_processor.middleware.format_rows$format_rows$fn__30063.invoke(format_rows.clj:26)”
“query_processor.middleware.binning$update_binning_strategy$fn__28589.invoke(binning.clj:172)”
“query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__31470.invoke(results_metadata.clj:89)”
“query_processor.middleware.resolve$resolve_middleware$fn__28118.invoke(resolve.clj:424)”
“query_processor.middleware.expand$expand_middleware$fn__29818.invoke(expand.clj:605)”
“query_processor.middleware.add_row_count_and_status$add_row_count_and_status$fn__28202.invoke(add_row_count_and_status.clj:14)”
“query_processor.middleware.driver_specific$process_query_in_context$fn__29949.invoke(driver_specific.clj:12)”
“query_processor.middleware.resolve_driver$resolve_driver$fn__31481.invoke(resolve_driver.clj:15)”
“query_processor.middleware.cache$maybe_return_cached_results$fn__28670.invoke(cache.clj:146)”
“query_processor.middleware.catch_exceptions$catch_exceptions$fn__29866.invoke(catch_exceptions.clj:58)”
“query_processor$process_query.invokeStatic(query_processor.clj:130)”
“query_processor$process_query.invoke(query_processor.clj:126)”
“query_processor$run_and_save_query_BANG_.invokeStatic(query_processor.clj:243)”
“query_processor$run_and_save_query_BANG_.invoke(query_processor.clj:237)”
“query_processor$fn__31515$process_query_and_save_execution_BANG___31520$fn__31521.invoke(query_processor.clj:283)”
“query_processor$fn__31515$process_query_and_save_execution_BANG___31520.invoke(query_processor.clj:269)”
“query_processor$fn__31539$process_query_and_save_with_max_BANG___31544$fn__31545.invoke(query_processor.clj:304)”
“query_processor$fn__31539$process_query_and_save_with_max_BANG___31544.invoke(query_processor.clj:300)”
“api.dataset$fn__38725$fn__38728$fn__38729.invoke(dataset.clj:50)”
“api.common$fn__20334$invoke_thunk_with_keepalive__20339$fn__20340$fn__20341.invoke(common.clj:400)”],
:query
{:type “native”,
:native
{:query
“SELECT count(*) AS count, date(from_unixtime(apt_seller_view_powerbi.seller_rd)) AS seller_rd\nFROM apt_seller_view_powerbi\nWHERE date(from_unixtime(apt_seller_view_powerbi.seller_rd)) BETWEEN ‘{{date_from}}’\n AND ‘{{date_to}}’\nGROUP BY date(from_unixtime(apt_seller_view_powerbi.seller_rd))\nORDER BY date(from_unixtime(apt_seller_view_powerbi.seller_rd)) ASC”,
:template_tags
{:date_to {:id “1f8d2be4-02ef-a137-3941-053d952fe6a2”, :name “date_to”, :display_name “Date to”, :type “date”},
:date_from {:id “fb6c565c-dafe-4291-59fd-37664fba824d”, :name “date_from”, :display_name “Date from”, :type “date”}}},
:parameters
[{:type “date/single”, :target [“variable” [“template-tag” “date_to”]], :value “2018-05-14”} {:type “date/single”, :target [“variable” [“template-tag” “date_from”]], :value “2018-05-14”}],
:constraints {:max-results 10000, :max-results-bare-rows 2000},
:info
{:executed-by 1,
:context :ad-hoc,
:card-id nil,
:nested? false,
:query-hash [22, 90, 82, -50, 93, 45, -27, 97, -101, 99, -26, -114, 22, -71, 127, 122, 108, -81, -120, 95, -42, 106, 51, 90, -109, -118, -101, -29, 83, 14, 121, 100],
:query-type “native”}},
:expanded-query nil}
What am I doing wrong?