英文:
Bigquery using case when for different datatypes in the same column
问题
我有一个列，其中包含整数和时间戳。我尝试使用CASE WHEN来检查数据类型，如果是整数，我想使用Timestamp_seconds进行转换，如果不是，我想使用默认值。
SELCT
CASE WHEN CAST(JSON_EXTRACT_SCALAR(payload, ''$.job.created_at'') AS INT64) IS NOT NULL
THEN TIMESTAMP_SECONDS(CAST(JSON_EXTRACT_SCALAR(payload, ''$.job.created_at'') AS INT64))
END AS job_created_timestamp FROM tableA ;
对于不是整数的值，会出现Bad int64 value: 2020-10-28T02:15:35.666Z的错误。我尝试了许多选项，如IS TRUE，ELSE用于CASE WHEN，但似乎都不起作用。请告诉我您的想法。谢谢。
英文:
I have column that has both integers and timestamp in a single column. I am trying to check with case when and if the datatype is integer I want to use Timestamp_seconds to cast it if not I want to use the default value.
SELCT
CASE WHEN CAST(JSON_EXTRACT_SCALAR(payload, '$.job.created_at') AS INT64) IS NOT NULL
THEN TIMESTAMP_SECONDS(CAST(JSON_EXTRACT_SCALAR(payload, '$.job.created_at') AS INT64))
END AS job_created_timestamp FROM tableA ;
is giving Bad int64 value: 2020-10-28T02:15:35.666Z for values that are not integers. And I tried many options like IS TRUE ,ELSE for case when and nothing seems to work. Please let me know for any thoughts. Thanks.
答案1
得分: 1
使用情况语句将列的Int64和时间戳值分离（作为单独的列）在With子句中，然后在Select语句中使用COALESCE(temp.integer_timestamp, temp.timestamp, NULL)合并它们。
英文:
The workaround is this :
Use case statement to separate out Int64 and timestamp values of a column in a With clause(as separate columns) and then merge them in the select statement using COALESCE(temp.integer_timestamp,temp.timestamp,NULL)
答案2
得分: 0
将@NickW的建议发布为答案，以提高社区的可见性。
使用SAFE_CAST而不是CAST函数来替换错误值为null值。
如果您想保护查询免受这些类型的错误干扰，可以使用SAFE_CAST。SAFE_CAST将运行时错误替换为NULL。然而，在静态分析期间，两个不可转换的非转换类型之间的不可能转换仍然会产生错误，因为查询无效。
英文:
Posting @NickW's recommendation as answer for a better visibility for the community.
Using SAFE_CAST instead of CAST function to replace the errors with null values instead.
>If you want to protect your queries from these types of errors, you can use SAFE_CAST. SAFE_CAST replaces runtime errors with NULLs. However, during static analysis, impossible casts between two non-castable types still produce an error because the query is invalid.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论