admin

如何在SAP HANA中进行日期转换的异常处理?

sql

问题摘要:

尝试转换为日期以便用户可以过滤数据时,HANA SQL或HANA Studio中的视图(计算,属性或分析)中处理无效数据的正确方法是什么?

在SAP的表KONM中,该字段KSTBM的数据类型为小数(15,3)。此字段是对还是错,以YYYYMMDDHHM.MSS格式存储日期值。(我不是系统的设计者。为什么有人将日期存储在小数字段而不是日期/时间字段超出此问题的范围。)

因此,有效值如下:

  • 201703290(2017年有效年03是一个有效月,而该月中的有效天29是该年。)
  • 201703301.130(2017年有效年03是一个有效月,该月该月中的有效日是30,以及11:30是有效时间)

存在,并且可以很容易地使用浇铸to_timestampdaydate或类似的功能。

不幸的是,此表中进行了一些错误的输入,导致无法将数据强制转换为日期时间,例如:

  • 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_Timestampdaydate函数时,会出现以下错误:

SAP DBTech JDBC:[2048]:列存储错误:搜索表错误:[6860]无效的日期,时间或时间戳记值;

或者

[303]:无效的DATE,TIME或TIMESTAMP值:

我同意这些日期无效…因此,我知道为什么会引发错误。

虽然我很想修复根本原因,然后更正错误的数据;目前还不是一种选择。不同的团队,不同的资源有不同的优先级。因此,这是他们要做的事情,但是我有需要WEBI报告的用户。而且由于存在错误数据,我仍然要处理它。

我想做的是在HANA Studio中创建一个Calculated_view,它可以成功处理这些错误的日期。但是我无法找到try
catch或其他类型的异常处理,这将使我基本上可以将这些日期设置为NULL,因此用户仍然可以获取其他相关数据,并且可以看到它们中包含一些不良数据。可以纠正的系统。

就目前而言,抛出此错误以来,在运行WEBI报告时,无法从Universe返回任何记录。我发现了一些选项,其中包括创建具有所有可能时间的日期/时间表…(希望您能明白为什么我不想这样做)或创建函数(但它没有特定的说明;我毕竟是HANA和Universes和WEBI的新用户,这就是为什么存在这个问题的原因)

这是可以在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,您会看到错误。

问题方面:

  1. 无论isBad为1还是0,如何才能使该查询无错误运行?
  2. 是否有一种方法/方式包括/不包括不良数据(也许在结果中将所有不良数据设置为NULL,然后可以作为用户的选择包括/排除空数据?
  3. 有没有一种方法可以在计算列中识别这些不良记录,因此我们不尝试将其转换为无效记录,而是在有效时进行尝试?
  4. 我的方法是完全错误的吗,我需要重新训练Oracle / MS SQL / MySQL的大脑以进行不同的思考吗?我会处理其他语言,或者isdate()在尝试强制转换之前尝试捕获或使用其他语言来进行验证…我只是在这里看不到这些选项(但我是新手,也许根本无法很好地使用帮助)

感谢您阅读我长期提出的问题。希望我已经提供了足够的细节。

我试图避免:


阅读 69

收藏
2021-06-07

共1个答案

admin

您可能需要为此使用tstmp_is_valid()函数:

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。

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。如果您有要保存但无效的字符串日期,则很有用。

2021-06-07