Orcle Filter Problem

DBL · June 18, 2019, 9:23am

Write SQL script query, add filter, display identifier invalid. There is no problem linking MySQL queries. May I ask how to solve it?

error log
六月 18 09:10:07 ERROR metabase.driver.sql-jdbc.execute :: nil
六月 18 09:10:07 WARN metabase.query-processor.middleware.process-userland-query :: Query failure {:status :failed,
:class java.sql.SQLException,
:error "ORA-00904: "METABASE"."TORGMANAGE"."ORGCODE": invalid identifier\n",
:stacktrace
("oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)"
"oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)"
"oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)"
"oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)"
"oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)"
"oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:813)"
"oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1049)"
"oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:854)"
"oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1154)"
"oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3370)"
"oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3415)"
"com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:379)"
"clojure.java.jdbc$execute_query_with_params.invokeStatic(jdbc.clj:1072)"
"clojure.java.jdbc$execute_query_with_params.invoke(jdbc.clj:1066)"
"clojure.java.jdbc$db_query_with_resultset_STAR_.invokeStatic(jdbc.clj:1081)"
"clojure.java.jdbc$db_query_with_resultset_STAR_.invoke(jdbc.clj:1075)"
"clojure.java.jdbc$query.invokeStatic(jdbc.clj:1164)"
"clojure.java.jdbc$query.invoke(jdbc.clj:1126)"
"--> driver.sql_jdbc.execute$cancelable_run_query$fn__62557.invoke(execute.clj:177)"
"driver.sql_jdbc.execute$do_with_ensured_connection.invokeStatic(execute.clj:154)"
"driver.sql_jdbc.execute$do_with_ensured_connection.invoke(execute.clj:152)"
"driver.sql_jdbc.execute$cancelable_run_query.invokeStatic(execute.clj:169)"
"driver.sql_jdbc.execute$cancelable_run_query.invoke(execute.clj:165)"
"driver.sql_jdbc.execute$run_query.invokeStatic(execute.clj:189)"
"driver.sql_jdbc.execute$run_query.invoke(execute.clj:185)"
"driver.sql_jdbc.execute$do_with_auto_commit_disabled.invokeStatic(execute.clj:236)"
"driver.sql_jdbc.execute$do_with_auto_commit_disabled.invoke(execute.clj:224)"
"driver.sql_jdbc.execute$do_in_transaction$fn__62576.invoke(execute.clj:241)"
"driver.sql_jdbc.execute$do_in_transaction.invokeStatic(execute.clj:240)"
"driver.sql_jdbc.execute$do_in_transaction.invoke(execute.clj:239)"
"driver.sql_jdbc.execute$run_query_without_timezone.invokeStatic(execute.clj:258)"
"driver.sql_jdbc.execute$run_query_without_timezone.invoke(execute.clj:257)"
"driver.sql_jdbc.execute$run_query_with_timezone.invokeStatic(execute.clj:270)"
"driver.sql_jdbc.execute$run_query_with_timezone.invoke(execute.clj:260)"
"driver.sql_jdbc.execute$execute_query$fn__62602.invoke(execute.clj:287)"
"driver.sql_jdbc.execute$do_with_try_catch.invokeStatic(execute.clj:216)"
"driver.sql_jdbc.execute$do_with_try_catch.invoke(execute.clj:210)"
"driver.sql_jdbc.execute$execute_query.invokeStatic(execute.clj:284)"
"driver.sql_jdbc.execute$execute_query.invoke(execute.clj:278)"
"driver.sql_jdbc$fn__62845.invokeStatic(sql_jdbc.clj:48)"
"driver.sql_jdbc$fn__62845.invoke(sql_jdbc.clj:47)"
"driver.oracle$eval541$fn__542.invoke(oracle.clj:282)"
"query_processor$fn__40384$execute_query__40389$fn__40390.invoke(query_processor.clj:61)"
"query_processor$fn__40384$execute_query__40389.invoke(query_processor.clj:55)"
"query_processor.middleware.mbql_to_native$mbql__GT_native$fn__34664.invoke(mbql_to_native.clj:36)"
"query_processor.middleware.annotate$result_rows_maps__GT_vectors$fn__32695.invoke(annotate.clj:368)"
"query_processor.middleware.annotate$add_column_info$fn__32600.invoke(annotate.clj:303)"
"query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__34144.invoke(cumulative_aggregations.clj:57)"
"query_processor.middleware.limit$limit$fn__34637.invoke(limit.clj:19)"
"query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__40249.invoke(results_metadata.clj:86)"
"query_processor.middleware.format_rows$format_rows$fn__34625.invoke(format_rows.clj:26)"
"query_processor.middleware.add_dimension_projections$add_remapping$fn__31752.invoke(add_dimension_projections.clj:234)"
"query_processor.middleware.add_row_count_and_status$add_row_count_and_status$fn__32146.invoke(add_row_count_and_status.clj:16)"
"query_processor.middleware.driver_specific$process_query_in_context$fn__34386.invoke(driver_specific.clj:12)"
"query_processor.middleware.resolve_driver$resolve_driver$fn__37215.invoke(resolve_driver.clj:15)"
"query_processor.middleware.bind_effective_timezone$bind_effective_timezone$fn__33550$fn__33551.invoke(bind_effective_timezone.clj:9)"
"util.date$call_with_effective_timezone.invokeStatic(date.clj:88)"
"util.date$call_with_effective_timezone.invoke(date.clj:77)"
"query_processor.middleware.bind_effective_timezone$bind_effective_timezone$fn__33550.invoke(bind_effective_timezone.clj:8)"
"query_processor.middleware.store$initialize_store$fn__40274$fn__40275.invoke(store.clj:11)"
"query_processor.store$do_with_new_store.invokeStatic(store.clj:35)"
"query_processor.store$do_with_new_store.invoke(store.clj:31)"
"query_processor.middleware.store$initialize_store$fn__40274.invoke(store.clj:10)"
"query_processor.middleware.async$async__GT_sync$fn__33146.invoke(async.clj:19)"
"query_processor.middleware.async_wait$runnable$fn__33277.invoke(async_wait.clj:71)"),
:query
{:type "native",
:native
{:query "select * from TORGMANAGE where {{OrgCode}}",
:template-tags {:OrgCode {:id "27df45b4-f818-c82a-5f8a-949122f75e36", :name "OrgCode", :display_name "Orgcode", :type "dimension", :dimension ["field-id" 492837], :widget-type "category"}}},
:parameters [{:type "category", :target ["dimension" ["template-tag" "OrgCode"]], :value ["00100101"]}],
:async? true,
:middleware {:add-default-userland-constraints? true, :userland-query? true},
:info
{:executed-by 1,
:context :ad-hoc,
:card-id nil,
:nested? false,
:query-hash [-106, 93, -76, 7, -78, -48, 9, 79, 33, 116, 0, -99, -51, 82, -94, 74, 123, -33, 114, 91, -118, -54, -50, -94, 21, 22, 89, 113, 110, 76, 60, -7]},
:constraints {:max-results 10000, :max-results-bare-rows 2000}}}

AndrewMBaines · June 18, 2019, 12:28pm

What are your filter settings?

DBL · June 18, 2019, 2:59pm

The field set by the data model is the entity name, and the filter brings in the classification.It’s a simple table.

AndrewMBaines · June 18, 2019, 3:17pm

Picture of the settings please.

DBL · June 18, 2019, 3:43pm

666
Fields are mapped to the fields of the query table, and the filter style is categorized. Metadata This field is set to the entity name.

flamber · June 18, 2019, 5:45pm

Hi @DBL
But your query is select * from TORGMANAGE where {{OrgCode}}, but it looks like you’re mapping a different table (METABASE).
You need to map a table that you are referencing in the SQL.

DBL · June 19, 2019, 12:24am

The database is newly created. I do map to the table fields of SQL queries.

flamber · June 19, 2019, 12:33am

@DBL
Okay, then activate query logging on your Oracle server to see what the full query looks like.
Could it be that you’re missing the schema?

select * from "Metabase"."TORGMANAGE" where {{OrgCode}}

DBL · June 19, 2019, 12:17pm

The statement you provide can be executed.But another way of writing errors,Improper execution of commands， is there a big difference between Oracle support and mysql, writing SQL query scripts in MySQL is very smooth.Is it in this table V $sql query log?

flamber · June 19, 2019, 2:20pm

@DBL

Great that it works.

Yes, there’s a big difference between Oracle and MySQL support. MySQL is part of the core, while Oracle is an add-on driver with dependencies.

I can see there’s a bug report on this already - go and upvote by clicking on the first post:

DBL · June 20, 2019, 8:28am

Do Oracle as a data source often present some temporary problems that cannot be solved? What data sources do you suggest we use?Supports migrating H2 program database to oracle?

flamber · June 20, 2019, 10:50am

@DBL
I don’t understand. Are you having other problems?
How can you change data source of your data? Don’t you have your application data in a specific database?
No, the internal metadata database currently only supports H2 or Postgres or MariaDB/MySQL.

DBL · June 20, 2019, 12:40pm

Thank you for your answer.

acborges · August 24, 2019, 9:50pm

Put it this way
select * from "Metabase"."TORGMANAGE" where 1 = 1 [[and {{OrgCode}}]]

Abs
JR