ABAP CDS视图中Substring匹配KEYDT与GABJA/GABPE无结果问题排查

核心问题是字段类型不匹配导致的比较逻辑失效：

• KEYDT字段通常是ABAP标准的DATS类型（存储格式为YYYYMMDD的日期型），而COBRB表的GABJA（年份）是NUMC(4)类型、GABPE（月份）是NUMC(2)类型。
• 你用SUBSTRING()从DATS字段提取的是字符串类型，直接和数值型的NUMC字段做相等比较时，ABAP CDS的隐式转换规则会导致匹配失败——比如字符串'2024'和数值2024在底层存储或比较逻辑上的处理不一致，即使字面量相同也会判定为不相等。

另外也可以排查下JOIN环节是否有数据过滤过度：比如c.objnr like 'OR%'是否误过滤了有效的工单对象编号，或者AUFK与COBRB的OBJNR关联是否存在数据不匹配的情况。

推荐以下几种针对性的修正方案：

方案1：使用CDS日期内置函数（最优解） #

直接用YEAR()和MONTH()函数从DATS类型字段提取年份、月份，返回值为数值类型，完美匹配GABJA和GABPE的类型：

define view ZCDS_COBRB 
as select from zcktfi_zar01 as a
inner join aufk as b on a.caufn = b.aufnr
inner join cobrb as c on b.objnr = c.objnr and c.objnr like 'OR%' 
{
   key a.bukrs       as bukrs,
   key a.afabe1      as afabe1,
   key a.keydt       as keydt,
   c.ps_psp_pnr      as posnr,
   a.pspid           as pspid,
   a.kostl           as kostl,
   a.stort           as stort,
   c.objnr           as caufn
} where a.bukrs = '3000' 
  AND c.gabja = YEAR(a.keydt) 
  AND c.gabpe = MONTH(a.keydt)

方案2：显式类型转换 #

如果必须用SUBSTRING，通过CAST()函数将提取的字符串转换为NUMC类型，确保两边比较的类型一致：

define view ZCDS_COBRB 
as select from zcktfi_zar01 as a
inner join aufk as b on a.caufn = b.aufnr
inner join cobrb as c on b.objnr = c.objnr and c.objnr like 'OR%' 
{
   key a.bukrs       as bukrs,
   key a.afabe1      as afabe1,
   key a.keydt       as keydt,
   c.ps_psp_pnr      as posnr,
   a.pspid           as pspid,
   a.kostl           as kostl,
   a.stort           as stort,
   c.objnr           as caufn
} where a.bukrs = '3000' 
  AND c.gabja = CAST( substring(a.keydt, 1, 4) AS NUMC(4) ) 
  AND c.gabpe = CAST( substring(a.keydt, 5, 2) AS NUMC(2) )

方案3：反向拼接匹配 #

将GABJA和GABPE拼接为YYYYMM格式，与KEYDT的前6位比较（注意如果GABPE是单数字月份，需要用LPAD补前导零）：

define view ZCDS_COBRB 
as select from zcktfi_zar01 as a
inner join aufk as b on a.caufn = b.aufnr
inner join cobrb as c on b.objnr = c.objnr and c.objnr like 'OR%' 
{
   key a.bukrs       as bukrs,
   key a.afabe1      as afabe1,
   key a.keydt       as keydt,
   c.ps_psp_pnr      as posnr,
   a.pspid           as pspid,
   a.kostl           as kostl,
   a.stort           as stort,
   c.objnr           as caufn
} where a.bukrs = '3000' 
  AND substring(a.keydt, 1, 6) = CONCAT( c.gabja, LPAD(c.gabpe, 2, '0') )

1. 单独查询ZCKTFI_ZAR01和COBRB的样本数据，确认KEYDT的年份/月份与GABJA/GABPE的数值字面量完全一致；
2. 检查AUFK的OBJNR是否与COBRB的OBJNR正确关联，避免JOIN环节丢失数据；
3. 临时移除c.objnr like 'OR%'条件，验证是否过滤掉了有效数据。

内容的提问来源于stack exchange，提问作者Silver_Ash