Expand SQL Script with Data Filter function

STT_MAE · December 7, 2020, 2:34pm

Hi all im new here

i have a running sql script for my database.
SELECT
COUNT(DISTINCT result.Mac Address) AS Total PCBA FAIL
FROM
(SELECT
pps_tester_data.Mac Address AS Mac Address,
SUM(IF((pps_tester_data.Test Status = ‘PASS’), 1, 0)) AS passes
FROM
pps_tester_data
GROUP BY pps_tester_data.Mac Address
HAVING (passes = 0)) result

this will run in metabase too.
But for my dashboard i have added a date filter.
now how to combine the SQL script and filter ?
i tryed many ways but without success.

i think the filter must be sonething like this:
WHERE (pps_tester_data.Test Date >= convert_tz(‘2020-01-01 00:00:00.000’, ‘Europe/Berlin’, @@session.time_zone)
AND pps_tester_data.Test Date < convert_tz(‘2025-01-02 00:00:00.000’, ‘Europe/Berlin’, @@session.time_zone))

but where to add ?

Thanks a lot
Markus

flamber · December 7, 2020, 7:52pm

Hi @STT_MAE
I would highly recommend that you read these two articles:
https://www.metabase.com/blog/sql-template-variables/index.html
https://www.metabase.com/blog/field-filters/index.html

STT_MAE · December 9, 2020, 3:52pm

Ok maybe someone can help me create a question for the problem.

i have a short test table with „Test Status“ (Pass/Fail) and „Mac Address“

FAIL D7:8F:C1:71:E8:BB
FAIL D7:8F:C1:71:E8:BB
PASS D7:8F:C1:71:E8:BB

PASS DA:BF:24:F3:28:96
PASS DA:BF:24:F3:28:96

FAIL F2:24:E6:1F:05:1E
FAIL F2:24:E6:1F:05:1E

FAIL CB:A9:37:9C:FD:08

now i want to make a question to count all data who only have Fail (one or more times) in combination with distinct Mac Address

FAIL D7:8F:C1:71:E8:BB
FAIL D7:8F:C1:71:E8:BB
PASS D7:8F:C1:71:E8:BB
= 0 (because of PASS and FAIL with the same Mac Address)

PASS DA:BF:24:F3:28:96
PASS DA:BF:24:F3:28:96
= 0 (because only PASS)

FAIL F2:24:E6:1F:05:1E
FAIL F2:24:E6:1F:05:1E
= 1 (because same Mac Address)

FAIL CB:A9:37:9C:FD:08
= 1

The problem is when the Mac Address has PASS and FAIL then to count 0.
Counting only passes or only fails are no problem.

can someone help me with this please

Thanks
Markus

flamber · December 9, 2020, 4:24pm

@STT_MAE I don’t understand. Can you show a screenshot of how you are trying to use the Notebook editor to create this?

STT_MAE · December 10, 2020, 6:17am

HI

i have this SQL script working in MYSQL Workbench. (This Screenshot is with more datasets)

Now I want to do the same as a question in Metabase and add that question to a dashboard with filters.

Thanks
Markus

STT_MAE · December 10, 2020, 7:46am

Yes i did it!

@flamber thanks for the links !

my result is:
SELECT
COUNT(DISTINCT result.Mac Address) AS Total PCBA FAIL
FROM
(SELECT
pps_tester_data.Mac Address AS Mac Address,
pps_tester_data.Test Date AS Test Date,
SUM(IF((pps_tester_data.Test Status = ‘PASS’), 1, 0)) AS passes
FROM
pps_tester_data
where pps_tester_data.Mac Address <> ‘’
[[and {{category}}]]

    GROUP BY `pps_tester_data`.`Mac Address`
    HAVING (passes = 0)) result;

thanks
Markus

STT_MAE · December 14, 2020, 9:14am

Hello again

ok this works all well until i enable the Embedding of this Dashboard.
if no Date filter is activated all is OK

But if i set a date range then the Total PCBA failed do not show anything.
this is in the embedded Dashboard only.

hmm maybe i have to do this question with a Metabase question instead of an SQL Script ?

Thanks
Markus

flamber · December 14, 2020, 10:56am

@STT_MAE
Post “Diagnostic Info” from Admin > Troubleshooting.
Post the detailed error, when you see the error on embedding - Admin > Troubleshooting > Logs.
And post the query for the question that is erroring.

STT_MAE · December 14, 2020, 12:18pm

@flamber

i only copied the one time segment where i produced the error

it looks like it has something to do with the wrong time zone.
But i think i set it right in the Admin settings.

[e8c104c9-78f6-40c4-90a1-c8c5e41507fc] 2020-12-14T13:08:12+01:00 ERROR metabase.driver.sql-jdbc.execute Zeitzone Europe/Berlin konnte nicht für Datenbank :mysql gesetzt werden.

java.sql.SQLTransientConnectionException: (conn=112) Unknown or incorrect time zone: 'Europe/Berlin'

at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:79)

at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:153)

at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:274)

at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:363)

at org.mariadb.jdbc.MariaDbStatement.execute(MariaDbStatement.java:501)

at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:75)

at metabase.driver.sql_jdbc.execute$set_time_zone_if_supported_BANG_.invokeStatic(execute.clj:127)

at metabase.driver.sql_jdbc.execute$set_time_zone_if_supported_BANG_.invoke(execute.clj:112)

at metabase.driver.sql_jdbc.execute$fn__76511.invokeStatic(execute.clj:159)

at metabase.driver.sql_jdbc.execute$fn__76511.invoke(execute.clj:154)

at clojure.lang.MultiFn.invoke(MultiFn.java:239)

at metabase.driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:388)

at metabase.driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:376)

at metabase.driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:385)

at metabase.driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:376)

at metabase.driver.sql_jdbc$fn__78126.invokeStatic(sql_jdbc.clj:49)

at metabase.driver.sql_jdbc$fn__78126.invoke(sql_jdbc.clj:47)

at clojure.lang.MultiFn.invoke(MultiFn.java:244)

at metabase.query_processor.context$executef.invokeStatic(context.clj:59)

at metabase.query_processor.context$executef.invoke(context.clj:48)

at metabase.query_processor.context.default$default_runf.invokeStatic(default.clj:69)

at metabase.query_processor.context.default$default_runf.invoke(default.clj:67)

at metabase.query_processor.context$runf.invokeStatic(context.clj:45)

at metabase.query_processor.context$runf.invoke(context.clj:39)

at metabase.query_processor.reducible$pivot.invokeStatic(reducible.clj:34)

at metabase.query_processor.reducible$pivot.invoke(reducible.clj:31)

at metabase.query_processor.middleware.mbql_to_native$mbql__GT_native$fn__47457.invoke(mbql_to_native.clj:26)

at metabase.query_processor.middleware.check_features$check_features$fn__46732.invoke(check_features.clj:42)

at metabase.query_processor.middleware.optimize_datetime_filters$optimize_datetime_filters$fn__47622.invoke(optimize_datetime_filters.clj:133)

at metabase.query_processor.middleware.auto_parse_filter_values$auto_parse_filter_values$fn__45533.invoke(auto_parse_filter_values.clj:44)

at metabase.query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__49188.invoke(wrap_value_literals.clj:149)

at metabase.query_processor.middleware.annotate$add_column_info$fn__45295.invoke(annotate.clj:575)

at metabase.query_processor.middleware.permissions$check_query_permissions$fn__46607.invoke(permissions.clj:70)

at metabase.query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__48140.invoke(pre_alias_aggregations.clj:40)

at metabase.query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__46805.invoke(cumulative_aggregations.clj:61)

at metabase.query_processor.middleware.resolve_joined_fields$resolve_joined_fields$fn__48377.invoke(resolve_joined_fields.clj:36)

at metabase.query_processor.middleware.resolve_joins$resolve_joins$fn__48696.invoke(resolve_joins.clj:183)

at metabase.query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__41020.invoke(add_implicit_joins.clj:254)

at metabase.query_processor.middleware.large_int_id$convert_id_to_string$fn__47418.invoke(large_int_id.clj:44)

at metabase.query_processor.middleware.limit$limit$fn__47443.invoke(limit.clj:38)

at metabase.query_processor.middleware.format_rows$format_rows$fn__47398.invoke(format_rows.clj:84)

at metabase.query_processor.middleware.desugar$desugar$fn__46871.invoke(desugar.clj:22)

at metabase.query_processor.middleware.binning$update_binning_strategy$fn__45892.invoke(binning.clj:229)

at metabase.query_processor.middleware.resolve_fields$resolve_fields$fn__46409.invoke(resolve_fields.clj:24)

at metabase.query_processor.middleware.add_dimension_projections$add_remapping$fn__40564.invoke(add_dimension_projections.clj:318)

at metabase.query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__40771.invoke(add_implicit_clauses.clj:141)

at metabase.query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__41169.invoke(add_source_metadata.clj:105)

at metabase.query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__48337.invoke(reconcile_breakout_and_order_by_bucketing.clj:98)

at metabase.query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__45480.invoke(auto_bucket_datetimes.clj:125)

at metabase.query_processor.middleware.resolve_source_table$resolve_source_tables$fn__46456.invoke(resolve_source_table.clj:46)

at metabase.query_processor.middleware.parameters$substitute_parameters$fn__48122.invoke(parameters.clj:114)

at metabase.query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__46508.invoke(resolve_referenced.clj:80)

at metabase.query_processor.middleware.expand_macros$expand_macros$fn__47127.invoke(expand_macros.clj:158)

at metabase.query_processor.middleware.add_timezone_info$add_timezone_info$fn__41200.invoke(add_timezone_info.clj:15)

at metabase.query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__49058.invoke(splice_params_in_response.clj:32)

at metabase.query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48348$fn__48352.invoke(resolve_database_and_driver.clj:33)

at metabase.driver$do_with_driver.invokeStatic(driver.clj:61)

at metabase.driver$do_with_driver.invoke(driver.clj:57)

at metabase.query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48348.invoke(resolve_database_and_driver.clj:27)

at metabase.query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__47345.invoke(fetch_source_query.clj:267)

at metabase.query_processor.middleware.store$initialize_store$fn__49067$fn__49068.invoke(store.clj:11)

at metabase.query_processor.store$do_with_store.invokeStatic(store.clj:46)

at metabase.query_processor.store$do_with_store.invoke(store.clj:40)

at metabase.query_processor.middleware.store$initialize_store$fn__49067.invoke(store.clj:10)

at metabase.query_processor.middleware.cache$maybe_return_cached_results$fn__46385.invoke(cache.clj:214)

at metabase.query_processor.middleware.validate$validate_query$fn__49076.invoke(validate.clj:10)

at metabase.query_processor.middleware.normalize_query$normalize$fn__47470.invoke(normalize_query.clj:22)

at metabase.query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__41038.invoke(add_rows_truncated.clj:36)

at metabase.query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__49043.invoke(results_metadata.clj:147)

at metabase.query_processor.middleware.constraints$add_default_userland_constraints$fn__46748.invoke(constraints.clj:42)

at metabase.query_processor.middleware.process_userland_query$process_userland_query$fn__48211.invoke(process_userland_query.clj:136)

at metabase.query_processor.middleware.catch_exceptions$catch_exceptions$fn__46691.invoke(catch_exceptions.clj:174)

at metabase.query_processor.reducible$async_qp$qp_STAR___39827$thunk__39828.invoke(reducible.clj:103)

at metabase.query_processor.reducible$async_qp$qp_STAR___39827$fn__39830.invoke(reducible.clj:108)

at clojure.core$binding_conveyor_fn$fn__5754.invoke(core.clj:2030)

at clojure.lang.AFn.call(AFn.java:18)

at java.base/java.util.concurrent.FutureTask.run(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.base/java.lang.Thread.run(Unknown Source)

Caused by: org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException: Unknown or incorrect time zone: 'Europe/Berlin'

at org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException.of(MariaDbSqlException.java:34)

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:194)

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:262)

at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:357)

... 76 more

Caused by: java.sql.SQLException: Unknown or incorrect time zone: 'Europe/Berlin'

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1688)

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1550)

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1513)

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:256)

... 77 more

the log goes on but there is too much text for one post.
Do you need more of the log ?

Thanks
Markus

flamber · December 14, 2020, 12:27pm

@STT_MAE No, that’s a different error caused by your MySQL database not having tzdata:
https://dba.stackexchange.com/questions/120945/how-do-i-resolve-this-error-error-1298-hy000-unknown-or-incorrect-time-zone

STT_MAE · December 14, 2020, 12:47pm

@flamber
OK
if it’s not the timezone error, what is causing my error?
do you need more of the log ?

thanks for your time and effort

Markus

flamber · December 14, 2020, 12:49pm

@STT_MAE I need to three things I asked for previously.

STT_MAE · December 14, 2020, 1:26pm

@flamber
Oh I missed that…

Diagnostic Info:
{
“browser-info”: {
“language”: “de-AT”,
“platform”: “Win32”,
“userAgent”: “Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.88 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “Cp1252”,
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “11.0.9+11”,
“java.vendor”: “AdoptOpenJDK”,
“java.vendor.url”: “https://adoptopenjdk.net/”,
“java.version”: “11.0.9”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “11.0.9+11”,
“os.name”: “Windows Server 2019”,
“os.version”: “10.0”,
“user.language”: “de”,
“user.timezone”: “Europe/Berlin”
},
“metabase-info”: {
“databases”: [
“h2”,
“mysql”
],
“hosting-env”: “unknown”,
“application-database”: “h2”,
“application-database-details”: {
“database”: {
“name”: “H2”,
“version”: “1.4.197 (2018-03-18)”
},
“jdbc-driver”: {
“name”: “H2 JDBC Driver”,
“version”: “1.4.197 (2018-03-18)”
}
},
“run-mode”: “prod”,
“version”: {
“date”: “2020-11-16”,
“tag”: “v0.37.2”,
“branch”: “release-x.37.x”,
“hash”: “25e5f70”
},
“settings”: {
“report-timezone”: “Europe/Berlin”
}
}
}

The Query:
SELECT
COUNT(DISTINCT result.Mac Address) AS Total PCBA FAIL
FROM

    (SELECT 
        `pps_tester_data`.`Mac Address` AS `Mac Address`,
        `pps_tester_data`.`Test Date` AS `Test Date`,
            SUM(IF((`pps_tester_data`.`Test Status` = 'PASS'), 1, 0)) AS `passes`
    FROM
        `pps_tester_data`
        where `pps_tester_data`.`Mac Address` <> ''
        [[and {{category}}]]

    GROUP BY `pps_tester_data`.`Mac Address`
    HAVING (passes = 0)) result;

the log is very big.
do you need more?
[e8c104c9-78f6-40c4-90a1-c8c5e41507fc] 2020-12-14T13:08:12+01:00 ERROR metabase.driver.sql-jdbc.execute Zeitzone Europe/Berlin konnte nicht für Datenbank :mysql gesetzt werden.

java.sql.SQLTransientConnectionException: (conn=112) Unknown or incorrect time zone: ‘Europe/Berlin’