如何在SAP HANA中进行日期转换的异常处理? [英] How does one handle exceptions in date casting in SAP's HANA?
问题描述
问题摘要:
在尝试转换为日期以便用户可以过滤数据的视图(计算，属性或分析)中的视图(计算，属性或分析)中，HANA SQL或HANA Studio中的正确方法是什么?
What is the proper way within HANA SQL or HANA Studio in a view (Calculation, attribute or Analytic) to handle invalid data when attempting to cast to a date so a user can filter the data?
在SAP表 KONM 中，字段KSTBM是以下类型的数据类型:十进制(15,3).此字段是对还是错，以YYYYMMDDHHM.MSS格式存储日期值. (我不是系统的设计者.为什么有人将日期存储在小数字段而不是日期/时间字段超出此问题的范围.)
In SAP's table KONM the field KSTBM is a datatype of decimal(15,3). This field right or wrong, stores a date value in YYYYMMDDHHM.MSS format. (I'm a user not a designer of the system. Why anyone stores a date in a decimal field instead of a oh a date/time field is beyond the purview of this question.)
有效值，例如:
- 201703290(2017年有效年03是一个有效月份，而当月29是该年该月的有效日期.)
- 201703301.130(yep 2017有效年03是该月的有效月份，30是该年该月的有效日期，以及11:30是有效时间)
存在，可以使用to_timestamp或daydate或类似函数轻松进行投射.
exist and can easily be cast using a to_timestamp or daydate or similar function.
不幸的是，此表中进行了一些错误的输入，导致无法将数据强制转换为日期时间，例如:
Unfortunately a few bad entries have been made into this table resulting in data that is unable to be cast to a date time such as:
- 201702290(无效的日期2月在2017年没有29天)
- 201713500(无效月份为12个月，而不是13个月)
- 201712312.400(无效时间00:00:00-23:59:59没有24)
- 201712310.060(无效的00:60分钟将是01:00)
- 201712310.090(无效的00:90分钟将是01:30)
在此类无效记录上使用to_Timestamp或daydate函数时，会出现以下错误:
When using to_Timestamp or daydate functions on such invalid records the following error is presented:
SAP DBTech JDBC:[2048]:列存储错误:搜索表错误:[6860]无效的日期，时间或时间戳值；
SAP DBTech JDBC: [2048]: column store error: search table error: [6860] invalid date, time or timestamp value;
或
[303]:无效的DATE，TIME或TIMESTAMP值:
[303]: invalid DATE, TIME or TIMESTAMP value:
我同意这些日期无效...因此，我知道为什么会引发错误.
I agree those are invalid dates... and thus I know why the error is thrown.
尽管我想修复根本原因，然后更正不良数据；目前还不是一种选择.不同的团队，不同的资源有不同的优先级.因此，这是他们要做的事情，但是我有需要WEBI报告的用户.而且由于存在错误数据，我仍然要处理它.
While I would love to fix the root cause and then correct the bad data; that isn't presently an option. Different teams, different resources different priorities. So it's on their list of things to do but I have users who need the WEBI reports; and since the bad data exists... I still have to handle it.
我想做的是在HANA Studio中创建一个Calculated_view，它可以成功处理这些错误的日期.但是我无法找到try catch或其他类型的异常处理，这将使我基本上可以将这些日期设置为NULL，因此用户仍然可以获取其他相关数据，并且可以看到它们中包含一些不良数据.可以纠正的系统.
What I'm trying to do is create a Calculated_view in HANA Studio which can handle these erroneous dates successfully. But I've not been able to find a try catch or other type of exception handling which would allow me to basically set these dates to NULL so the user still gets the other relevant data and are able to see they have some bad data in the system that the could correct.
自从抛出此错误以来，在运行WEBI报告时，无法从Universe返回任何记录.我发现了一些选项，其中包括创建具有所有可能时间的日期/时间表...(希望您能明白为什么我不想这样做)或创建函数(但它没有特定的说明；我毕竟是HANA and Universes和WEBI的新用户，这就是问题存在的原因)
As it stands since this error is thrown, no records are able to be returned from the universe when the WEBI report is run. I've found a few options that involve creating a date/time table with all possible times.... (I hope you can see why I don't want to do that) or creating a function (but it lacks specific directions; I'm afterall a new user to HANA and Universes and WEBI which is why the question exists)
以下是可以在HANA Studio中运行的示例:
Here's an example that could be run in HANA Studio:
WITH MyExample as (SELECT 201701011.230 as KSTBM, 0 isBad from dummy union all
SELECT 201702301.000 as KSTBM, 1 isBad from dummy union all
SELECT 201702171.230 as KSTBM, 0 isBad from dummy union all
SELECT 201702192.400 as KSTBM, 1 isBad from dummy)
SELECT to_timestamp(To_DECIMAL(KSTBM*100000,15,0)) TS,
isBad
FROM MyExample A
WHERE isBad = 1
将isBad更改为0即可使用；将isBad更改为1，您会看到错误.
Change isBad to 0 and it works; change isBad to 1 and you see the error.
问题方面:
- 无论isBad为1还是0，如何使该查询无错误运行?
- 是否存在一种方法/方式来包括/不包括不良数据(也许在结果中将所有不良数据设置为NULL，然后可以作为用户的选择包括/排除空数据?
- 是否有一种方法可以在计算所得的列中识别这些不良记录，因此我们不尝试将其转换为无效记录，而是在有效时进行尝试?
- 我的方法是完全错误的吗，我需要重新训练Oracle/MS SQL/MySQL的思维方式来思考吗?我会处理其他语言的异常，或者尝试捕获或使用
isdate()来尝试强制转换之前检查其有效性...我只是在这里看不到这些选项(但我是新手，也许根本无法使用帮助很好)
- How can I get this query to run without error regardless of isBad being 1 or 0?
- Is there a method/manner to include/not include the bad data (perhaps set all bad data to NULL in the result and null data can then be included/excluded as a user's option?
- is there a way in a view to identify these bad records in a calculated column so we don't try and convert them if invalid and do try when valid?
- Is my approach simply wrong and I need to retrain my Oracle/MS SQL/MySQL brain to think differently? Other languages I'd handle exceptions, or try catch or use
isdate()to check for valid before attempting to cast... I just don't see those options here (but I'm new and perhaps simply unable to use the help very well yet)
感谢您阅读我长期提出的问题.希望我提供了足够的细节.
Thanks for reading my long drawn out question. Hopefully I've provided sufficient details.
我正试图避免:
- https://blogs.sap.com/2017/05/10/to_date-to-isdate-udf-to-check-when-string-is-date-or-not/
- 并且DATE_CHECK_PLAUSIBILITY是一种ABAP函数(我正在寻找一个SQL/HANA Studio)，但这是正确的方法....我认为...
推荐答案
您可能要为此使用tstmp_is_valid()函数:
You might want to use the tstmp_is_valid() function for this:
WITH MyExample as (SELECT 201701011.230 as KSTBM, 0 isBad from dummy union all
SELECT 201702301.000 as KSTBM, 1 isBad from dummy union all
SELECT 201702171.230 as KSTBM, 0 isBad from dummy union all
SELECT 201702192.400 as KSTBM, 1 isBad from dummy)
SELECT KSTBM,
tstmp_is_valid(KSTBM*100000),
isBad
FROM MyExample A;
KSTBM TSTMP_IS_VALID(KSTBM*100000) ISBAD
201,701,011.23 1 0
201,702,301 0 1
201,702,171.23 1 0
201,702,192.4 0 1
请参见 CDS文档.
还有一个函数:DATS_IS_VALID("STRINGDATE")将评估日期并返回1或0.如果date是有效日期，则返回1.
There's also a function: DATS_IS_VALID("STRINGDATE") which will evaluate a date and return a 1 or 0. 1 being if date is a valid date.
WITH CTE AS
(SELECT '00000000' as STRINGDATE from dummy union all
SELECT '20190101' from dummy union all
SELECT '20190230' from dummy union all
SELECT '20191301' from dummy union all
SELECT '20191232' from dummy union all
SELECT '20190228' from dummy union all
SELECT '20200228' from dummy union all
SELECT '20200229' from dummy )
SELECT StringDate, DATS_IS_VALID("STRINGDATE") isValid, case when DATS_IS_VALID("STRINGDATE") =1 then
cast("STRINGDATE" as date) else cast(null as date) end RightDataType from CTE;
在上面的示例中，我们仅将日期转换为有效日期，然后在结果中无效时将其设置为null.如果您有要保存但无效的字符串日期，则很有用.
In the above example we simply convert a date to a valid date and set it to null when not valid in the results. Useful if you have string dates that are getting saved but not valid.
这篇关于如何在SAP HANA中进行日期转换的异常处理?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
