Orcle Filter Problem

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}}}

What are your filter settings?

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

Picture of the settings please.

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.

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.

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

@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}}

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?

@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 :+1: on the first post:

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?

@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.

Thank you for your answer.:slightly_smiling_face:

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

Abs
JR