Hi, I'm trying to solidify some data (2 columns of 2 different types) into 1 column. It is only possible to use plain SQ because CASTING is not supported in custom columns.
Is it something planned?
The big issue I encounter with custom queries is that if the model the question is based on changes, then I have to manually update the custom query to reflect the changes in the base model.
Or is there a way around that?
Best,
Nicolas
You are right, there's currently no casting/convert functions available. When you say 2 different types what types are we talking about and can you share an example of each?
Thanks,
Those are 2 postgress enums. Basically up until last month we were using "scan types" and now we use the "products" and I wanted to normalize the "scan types" into a "product" somehow.
Thanks!
Can you share the output of those two columns so i can understand what do they return. Since if i try with two strings it's working:
Even date works well
Some "older" cells (product time older than a month old) are empty though, not sure if that could be a problem?
Can you share Admin -> Troubleshooting -> Diagnostic Info
{
"browser-info": {
"language": "en-GB",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/112.0.0.0 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.18+10",
"java.vendor": "Eclipse Adoptium",
"java.vendor.url": "https://adoptium.net/",
"java.version": "11.0.18",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.18+10",
"os.name": "Linux",
"os.version": "5.4.231-137.341.amzn2.x86_64",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"h2",
"postgres",
"mongo",
"sqlite"
],
"hosting-env": "unknown",
"application-database": "h2",
"application-database-details": {
"database": {
"name": "H2",
"version": "2.1.212 (2022-04-09)"
},
"jdbc-driver": {
"name": "H2 JDBC Driver",
"version": "2.1.212 (2022-04-09)"
}
},
"run-mode": "prod",
"version": {
"date": "2023-04-05",
"tag": "v0.46.1",
"branch": "release-x.46.x",
"hash": "12a6e1d"
},
"settings": {
"report-timezone": "Europe/Amsterdam"
}
}
}
I tested this on column with empty rows but still able to make it work
Are you able to share the DDL maybe? will try to have the same schema as you have
Thanks @TonyC How can I extract the DDL from Metabase?
FYI, I tried on fresh raw data with less columns and I get the same error:
Looking at the logs, I think the problem may be because the 2 columns I want to Coalesce have a different type in the database. They have the same "base" and "effective" types but have different types in the DB...
:error "ERROR: COALESCE could not convert type \"ScanType\" to \"ProductType\"\n Position: 610",
:row_count 0,
:running_time 0,
:preprocessed
{:type :query,
:query
{:source-table 71,
:fields
[[:field 9506 nil]
[:field 9540 nil]
[:field 9557 nil]
[:expression "normalized"]
[:field 9590 {:join-alias "TaskRequest"}]
[:field 9588 {:join-alias "TaskRequest"}]
[:field 9592 {:join-alias "TaskRequest"}]],
:expressions {"normalized" [:coalesce [:field 9592 {:join-alias "TaskRequest"}] [:field 9557 nil]]},
:filter
[:and
[:or
[:=
[:field 9557 nil]
[:value
"OSS"
{:base_type :type/PostgresEnum,
:effective_type :type/PostgresEnum,
:coercion_strategy nil,
:semantic_type :type/Category,
:database_type "ScanType",
:name "scan_type"}]]
[:=
[:field 9557 nil]
[:value
"CBCT"
{:base_type :type/PostgresEnum,
:effective_type :type/PostgresEnum,
:coercion_strategy nil,
:semantic_type :type/Category,
:database_type "ScanType",
:name "scan_type"}]]]
[:or
[:=
[:field 9592 {:join-alias "TaskRequest"}]
[:value
"OSS"
{:base_type :type/PostgresEnum,
:effective_type :type/PostgresEnum,
:coercion_strategy nil,
:semantic_type :type/Category,
:database_type "ProductType",
:name "product_type"}]]
[:=
[:field 9592 {:join-alias "TaskRequest"}]
[:value
"CBCT"
{:base_type :type/PostgresEnum,
:effective_type :type/PostgresEnum,
:coercion_strategy nil,
:semantic_type :type/Category,
:database_type "ProductType",
:name "product_type"}]]]],
:limit 10,
:joins
[{:alias "TaskRequest",
:strategy :left-join,
:fields
[[:field 9590 {:join-alias "TaskRequest"}]
[:field 9588 {:join-alias "TaskRequest"}]
[:field 9592 {:join-alias "TaskRequest"}]],
:source-table 75,
:condition [:= [:field 9540 nil] [:field 9588 {:join-alias "TaskRequest"}]]}]},
:database 10,
:middleware {:js-int-to-string? true, :add-default-userland-constraints? true},
:info {:executed-by 2, :context :ad-hoc}},
:data {:rows [], :cols []}}
and the query with some obfuscation - the first COALESCE if the one that fails.
SELECT "source"."id" AS "id",
"source"."scan_type" AS "scan_type",
"source"."TaskRequest__product_type" AS "TaskRequest__product_type",
"source"."Normalized type" AS "Normalized type"
FROM
(SELECT "source"."id" AS "id",
<…>
COALESCE("source"."TaskRequest__product_type", "source"."scan_type") AS "Normalized type"
FROM
(SELECT "source"."id" AS "id",
<…>
"source"."Errored" AS "Errored",
"source"."TaskRequest__id" AS "TaskRequest__id",
"source"."TaskRequest__task_id" AS "TaskRequest__task_id",
"source"."TaskRequest__product_type" AS "TaskRequest__product_type",
FROM
(SELECT "public"."Task"."id" AS "id",
<…>
CASE
WHEN (X)
OR (Y) THEN 'True'
ELSE 'False'
END AS "Errored",
CONCAT(‘some url, "public"."Task"."id") AS "Preview",
CASE
WHEN ("public"."Task"."state" <> CAST('PROCESSING' AS "TaskState"))
OR ("public"."Task"."state" IS NULL) THEN CAST(TRUNC(extract(epoch
FROM "public"."Task"."completed_at") - extract(epoch
FROM "public"."Task"."uploaded_at")) AS integer)
ELSE 0
END AS "TTLB",
CASE
WHEN ("public"."Task"."state" <> CAST('PROCESSING' AS "TaskState"))
OR ("public"."Task"."state" IS NULL) THEN CAST(TRUNC(extract(epoch
FROM "public"."Task"."completed_at") - extract(epoch
FROM "public"."Task"."uploaded_at")) AS integer)
ELSE 0
END - (CAST(COALESCE("public"."Task"."upload_time_ms", 0) AS float) / 1000.0) AS "Inference time",
<…>
"TaskRequest"."product_type" AS "TaskRequest__product_type",
<…>
FROM "public"."Task"
LEFT JOIN "public"."TaskRequest" AS "TaskRequest" ON "public"."Task"."id" = "TaskRequest"."task_id") AS "source") AS "source") AS "source"
LIMIT 1048575