kirean
September 25, 2023, 2:33pm
1
Hi
New user here.
Trying to figure out how to use models in sql query
I have created a simple model in metabase using the GUI, this is the sql code:
SELECT
When I try to create a sql query using it I get:
select * from {{#138-ny-test-model }}
I don't understand the syntax error in this case. Any help?
Luiggi
October 3, 2023, 8:05pm
2
please post troubleshooting info
kirean
October 5, 2023, 12:02pm
3
{
"browser-info": {
"language": "en-US",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/116.0.0.0 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "Cp1252",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.20.1+1-LTS",
"java.vendor": "Azul Systems, Inc.",
"java.vendor.url": "http://www.azul.com/",
"java.version": "11.0.20.1",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.20.1+1-LTS",
"os.name": "Windows Server 2022",
"os.version": "10.0",
"user.language": "en",
"user.timezone": "Europe/Stockholm"
},
"metabase-info": {
"databases": [
"sqlserver",
"postgres"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "15.3"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.5.4"
}
},
"run-mode": "prod",
"version": {
"date": "2023-10-03",
"tag": "v0.47.3",
"branch": "?",
"hash": "4202328"
},
"settings": {
"report-timezone": "Europe/Stockholm"
}
}
}
kirean
October 5, 2023, 12:04pm
4
{:database_id 2,
:started_at #t "2023-10-05T14:01:17.997921100+02:00[Europe/Stockholm]",
:via
[{:status :failed,
:class clojure.lang.ExceptionInfo,
:error "Error executing query: Incorrect syntax near ')'.",
:stacktrace
["--> driver.sql_jdbc.execute$execute_reducible_query$fn__80997$fn__80998.invoke(execute.clj:681)"
"driver.sql_jdbc.execute$execute_reducible_query$fn__80997.invoke(execute.clj:678)"
"driver.sql_jdbc.execute$fn__80798$fn__80799.invoke(execute.clj:384)"
"driver.sql_jdbc.execute$fn__80766$do_with_resolved_connection__80767.invoke(execute.clj:334)"
"driver.sql_jdbc.execute$fn__80798.invokeStatic(execute.clj:378)"
"driver.sql_jdbc.execute$fn__80798.invoke(execute.clj:376)"
"driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:672)"
"driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:661)"
"driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:669)"
"driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:661)"
"driver.sql_jdbc$fn__115855.invokeStatic(sql_jdbc.clj:72)"
"driver.sql_jdbc$fn__115855.invoke(sql_jdbc.clj:70)"
"query_processor.context$executef.invokeStatic(context.clj:60)"
"query_processor.context$executef.invoke(context.clj:49)"
"query_processor.context.default$default_runf.invokeStatic(default.clj:68)"
"query_processor.context.default$default_runf.invoke(default.clj:66)"
"query_processor.context$runf.invokeStatic(context.clj:46)"
"query_processor.context$runf.invoke(context.clj:40)"
"query_processor.reducible$identity_qp.invokeStatic(reducible.clj:15)"
"query_processor.reducible$identity_qp.invoke(reducible.clj:12)"
"query_processor.middleware.cache$maybe_return_cached_results$maybe_return_cached_results_STAR___77052.invoke(cache.clj:224)"
"query_processor.middleware.permissions$check_query_permissions$fn__72635.invoke(permissions.clj:126)"
"query_processor.middleware.mbql_to_native$mbql__GT_native$fn__76177.invoke(mbql_to_native.clj:24)"
"query_processor$fn__78285$combined_post_process__78290$combined_post_process_STAR___78291.invoke(query_processor.clj:260)"
"query_processor$fn__78285$combined_pre_process__78286$combined_pre_process_STAR___78287.invoke(query_processor.clj:257)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__76963$fn__76968.invoke(resolve_database_and_driver.clj:36)"
"driver$do_with_driver.invokeStatic(driver.clj:93)"
"driver$do_with_driver.invoke(driver.clj:88)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__76963.invoke(resolve_database_and_driver.clj:35)"
"query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__72945.invoke(fetch_source_query.clj:316)"
"query_processor.middleware.store$initialize_store$fn__73126$fn__73127.invoke(store.clj:12)"
"query_processor.store$do_with_store.invokeStatic(store.clj:56)"
"query_processor.store$do_with_store.invoke(store.clj:50)"
"query_processor.middleware.store$initialize_store$fn__73126.invoke(store.clj:11)"
"query_processor.middleware.normalize_query$normalize$fn__77259.invoke(normalize_query.clj:36)"
"query_processor.middleware.constraints$add_default_userland_constraints$fn__74653.invoke(constraints.clj:54)"
"query_processor.middleware.process_userland_query$process_userland_query$fn__77188.invoke(process_userland_query.clj:151)"
"query_processor.middleware.catch_exceptions$catch_exceptions$fn__77585.invoke(catch_exceptions.clj:171)"
"query_processor.reducible$async_qp$qp_STAR___66802$thunk__66804.invoke(reducible.clj:103)"
"query_processor.reducible$async_qp$qp_STAR___66802.invoke(reducible.clj:109)"
"query_processor.reducible$async_qp$qp_STAR___66802.invoke(reducible.clj:94)"
"query_processor.reducible$sync_qp$qp_STAR___66814.doInvoke(reducible.clj:129)"
"query_processor$process_userland_query.invokeStatic(query_processor.clj:383)"
"query_processor$process_userland_query.doInvoke(query_processor.clj:379)"
"query_processor$fn__78334$process_query_and_save_execution_BANG___78343$fn__78346.invoke(query_processor.clj:394)"
"query_processor$fn__78334$process_query_and_save_execution_BANG___78343.invoke(query_processor.clj:387)"
"query_processor$fn__78379$process_query_and_save_with_max_results_constraints_BANG___78388$fn__78391.invoke(query_processor.clj:406)"
"query_processor$fn__78379$process_query_and_save_with_max_results_constraints_BANG___78388.invoke(query_processor.clj:399)"
"api.dataset$run_query_async$fn__100141.invoke(dataset.clj:74)"
"query_processor.streaming$streaming_response_STAR_$fn__61392$fn__61393.invoke(streaming.clj:166)"
"query_processor.streaming$streaming_response_STAR_$fn__61392.invoke(streaming.clj:165)"
"async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:69)"
"async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:67)"
"async.streaming_response$do_f_async$task__39661.invoke(streaming_response.clj:88)"],
:error_type :invalid-query,
:ex-data
{:driver :sqlserver,
:sql
["-- Metabase:: userID: 33 queryType: native queryHash: 3835b1923c67c292fc7bba80718e9ab9fad721e00f0330121276e06004bd4bde"
"select"
" *"
"from"
" ("
" SELECT"
" TOP 1048575 \"dbo\".\"requests_uit\".\"id\" AS \"id\","
" \"dbo\".\"requests_uit\".\"exportInstanceId\" AS \"exportInstanceId\","
" \"dbo\".\"requests_uit\".\"displayId\" AS \"displayId\","
" \"dbo\".\"requests_uit\".\"created_col4\" AS \"created_col4\","
" \"dbo\".\"requests_uit\".\"updated_col5\" AS \"updated_col5\","
" \"dbo\".\"requests_uit\".\"sys_subject_col36\" AS \"sys_subject_col36\""
" FROM"
" \"dbo\".\"requests_uit\""
" )"],
:params [],
:type :invalid-query}}],
:state "S0001",
:error_type :invalid-query,
:json_query
{:type "native",
:native
{:query "select * from {{#138-ny-test-model}}\n\n",
:template-tags
{:#138-ny-test-model
{:type "card",
:name "#138-ny-test-model",
:id "04ccf5e9-56eb-4b57-a889-be06f12f8800",
:card-id 138,
:display-name "#138 Ny Test Model"}}},
:database 2,
:parameters [],
:middleware {:js-int-to-string? true, :add-default-userland-constraints? true}},
:status :failed,
:class com.microsoft.sqlserver.jdbc.SQLServerException,
:stacktrace
["com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)"
"com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1695)"
"com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:620)"
"com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:539)"
"com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7685)"
"com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4048)"
"com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:272)"
"com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:246)"
"com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:457)"
"com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:1471)"
"--> driver.sql_jdbc.execute$fn__80920.invokeStatic(execute.clj:552)"
"driver.sql_jdbc.execute$fn__80920.invoke(execute.clj:550)"
"driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invokeStatic(execute.clj:565)"
"driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invoke(execute.clj:561)"
"driver.sql_jdbc.execute$execute_reducible_query$fn__80997$fn__80998.invoke(execute.clj:679)"
"driver.sql_jdbc.execute$execute_reducible_query$fn__80997.invoke(execute.clj:678)"
"driver.sql_jdbc.execute$fn__80798$fn__80799.invoke(execute.clj:384)"
"driver.sql_jdbc.execute$fn__80766$do_with_resolved_connection__80767.invoke(execute.clj:334)"
"driver.sql_jdbc.execute$fn__80798.invokeStatic(execute.clj:378)"
"driver.sql_jdbc.execute$fn__80798.invoke(execute.clj:376)"
"driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:672)"
"driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:661)"
"driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:669)"
"driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:661)"
"driver.sql_jdbc$fn__115855.invokeStatic(sql_jdbc.clj:72)"
"driver.sql_jdbc$fn__115855.invoke(sql_jdbc.clj:70)"
"query_processor.context$executef.invokeStatic(context.clj:60)"
"query_processor.context$executef.invoke(context.clj:49)"
"query_processor.context.default$default_runf.invokeStatic(default.clj:68)"
"query_processor.context.default$default_runf.invoke(default.clj:66)"
"query_processor.context$runf.invokeStatic(context.clj:46)"
"query_processor.context$runf.invoke(context.clj:40)"
"query_processor.reducible$identity_qp.invokeStatic(reducible.clj:15)"
"query_processor.reducible$identity_qp.invoke(reducible.clj:12)"
"query_processor.middleware.cache$maybe_return_cached_results$maybe_return_cached_results_STAR___77052.invoke(cache.clj:224)"
"query_processor.middleware.permissions$check_query_permissions$fn__72635.invoke(permissions.clj:126)"
"query_processor.middleware.mbql_to_native$mbql__GT_native$fn__76177.invoke(mbql_to_native.clj:24)"
"query_processor$fn__78285$combined_post_process__78290$combined_post_process_STAR___78291.invoke(query_processor.clj:260)"
"query_processor$fn__78285$combined_pre_process__78286$combined_pre_process_STAR___78287.invoke(query_processor.clj:257)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__76963$fn__76968.invoke(resolve_database_and_driver.clj:36)"
"driver$do_with_driver.invokeStatic(driver.clj:93)"
"driver$do_with_driver.invoke(driver.clj:88)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__76963.invoke(resolve_database_and_driver.clj:35)"
"query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__72945.invoke(fetch_source_query.clj:316)"
"query_processor.middleware.store$initialize_store$fn__73126$fn__73127.invoke(store.clj:12)"
"query_processor.store$do_with_store.invokeStatic(store.clj:56)"
"query_processor.store$do_with_store.invoke(store.clj:50)"
"query_processor.middleware.store$initialize_store$fn__73126.invoke(store.clj:11)"
"query_processor.middleware.normalize_query$normalize$fn__77259.invoke(normalize_query.clj:36)"
"query_processor.middleware.constraints$add_default_userland_constraints$fn__74653.invoke(constraints.clj:54)"
"query_processor.middleware.process_userland_query$process_userland_query$fn__77188.invoke(process_userland_query.clj:151)"
"query_processor.middleware.catch_exceptions$catch_exceptions$fn__77585.invoke(catch_exceptions.clj:171)"
"query_processor.reducible$async_qp$qp_STAR___66802$thunk__66804.invoke(reducible.clj:103)"
"query_processor.reducible$async_qp$qp_STAR___66802.invoke(reducible.clj:109)"
"query_processor.reducible$async_qp$qp_STAR___66802.invoke(reducible.clj:94)"
"query_processor.reducible$sync_qp$qp_STAR___66814.doInvoke(reducible.clj:129)"
"query_processor$process_userland_query.invokeStatic(query_processor.clj:383)"
"query_processor$process_userland_query.doInvoke(query_processor.clj:379)"
"query_processor$fn__78334$process_query_and_save_execution_BANG___78343$fn__78346.invoke(query_processor.clj:394)"
"query_processor$fn__78334$process_query_and_save_execution_BANG___78343.invoke(query_processor.clj:387)"
"query_processor$fn__78379$process_query_and_save_with_max_results_constraints_BANG___78388$fn__78391.invoke(query_processor.clj:406)"
"query_processor$fn__78379$process_query_and_save_with_max_results_constraints_BANG___78388.invoke(query_processor.clj:399)"
"api.dataset$run_query_async$fn__100141.invoke(dataset.clj:74)"
"query_processor.streaming$streaming_response_STAR_$fn__61392$fn__61393.invoke(streaming.clj:166)"
"query_processor.streaming$streaming_response_STAR_$fn__61392.invoke(streaming.clj:165)"
"async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:69)"
"async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:67)"
"async.streaming_response$do_f_async$task__39661.invoke(streaming_response.clj:88)"],
:card_id nil,
:context :ad-hoc,
:error "Incorrect syntax near ')'.",
:row_count 0,
:running_time 0,
:data {:rows [], :cols []}}
kirean
October 12, 2023, 9:45pm
5
solved with
select * from {{#138-ny-test-model }} as model_alias