如何使CDS视图查询实现大小写不敏感？

针对数据库字段值与搜索输入大小写不匹配导致无结果的问题，ABAP CDS提供了几种直接的语法方案来实现大小写不敏感匹配：

方案1：统一转换字段大小写（推荐用于固定匹配场景） #

在CDS视图中将name1字段转换为大写（或小写）后输出，这样用户搜索时只需保证拼写正确，无论输入大小写都能匹配。同时需补充完整group by子句（原代码中该部分缺失，必须包含所有非聚合字段）：

define view ZCDSV_XXX_XXXX 
as select from epkuatv as a
inner join epku_adr1cp as b
on a.epkunnr = b.epkunnr   
{
key a.epkunnr as EUNumber,
upper(b.name1) as EndUserName1, -- 转换为大写输出，消除大小写差异
b.name2 as EndUserName2,
b.street as Address,
b.str_suppl1 as AddressStreet2,
b.city1 as City,
b.region as State Province,
b.post_code1 as Postal Code,
cast(max( case a.atinn when '0000001361' then atwrt end ) as 
abap.char(4)) as End User Group, 
max( case a.atinn when '0000001361' then atinn end ) as 
EUGRPCHAR,
cast(max( case a.atinn when '0000009763' then atwrt 
when '0000009639' then atwrt end ) as abap.char(4)) as 
EndUserParGroup,
max( case a.atinn when '0000009763' then atinn 
when '0000009639' then atinn end ) as EUPARGRPCHAR         

}
group by a.epkunnr, b.name1, b.name2, b.street, b.str_suppl1, b.city1, b.region, b.post_code1

方案2：在查询条件中使用大小写不敏感匹配 #

如果不需要修改视图输出的字段格式，可以在调用CDS视图时，通过upper()或lower()函数统一转换搜索值和字段值：

SELECT * FROM ZCDSV_XXX_XXXX
WHERE upper(EndUserName1) = upper('Starbucks');

或者使用ABAP CDS原生的CONTAINS函数并指定CASE INSENSITIVE选项：

SELECT * FROM ZCDSV_XXX_XXXX
WHERE contains(EndUserName1, 'Starbucks', case insensitive);

方案3：通过CDS参数实现动态大小写不敏感过滤 #

如果需要在CDS视图中内置过滤逻辑，可以定义参数并在WHERE子句中统一处理大小写：

define view ZCDSV_XXX_XXXX 
with parameters
  @EndUserNameFilter: abap.char(40)
as select from epkuatv as a
inner join epku_adr1cp as b
on a.epkunnr = b.epkunnr   
where upper(b.name1) = upper(:EndUserNameFilter) -- 参数与字段统一转大写匹配
{
key a.epkunnr as EUNumber,
b.name1 as EndUserName1,
b.name2 as EndUserName2,
b.street as Address,
b.str_suppl1 as AddressStreet2,
b.city1 as City,
b.region as State Province,
b.post_code1 as Postal Code,
cast(max( case a.atinn when '0000001361' then atwrt end ) as 
abap.char(4)) as End User Group, 
max( case a.atinn when '0000001361' then atinn end ) as 
EUGRPCHAR,
cast(max( case a.atinn when '0000009763' then atwrt 
when '0000009639' then atwrt end ) as abap.char(4)) as 
EndUserParGroup,
max( case a.atinn when '0000009763' then atinn 
when '0000009639' then atinn end ) as EUPARGRPCHAR         

}
group by a.epkunnr, b.name1, b.name2, b.street, b.str_suppl1, b.city1, b.region, b.post_code1

调用时传入参数即可实现大小写不敏感匹配：

SELECT * FROM ZCDSV_XXX_XXXX(EndUserNameFilter = 'Starbucks');

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