I have the below CDS view. I need to do 1 more thing that the business has requested. Column Name1 has value 'STARBUCKS' in the db table and when user tries to search with 'Starbucks' it does not return anything. Is there a syntax that I can use to make it case insensitive, so it returns values if we just spell it right and not just when we enter it in Upper cases.
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,
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
-
Did you checked to_upper and upper sentences? Not sure which is SQL and which one is ABAP, but using both you can get that info (if your system is newer enough)VXLozano– VXLozano2025年06月05日 15:12:44 +00:00Commented Jun 5, 2025 at 15:12
-
Please edit your question and remove the linked image of code, and instead add your code as formatted text into the body of your question.devlin carnate– devlin carnate2025年06月05日 15:15:29 +00:00Commented Jun 5, 2025 at 15:15
-
I just completed the formatting. The column endusername1 has values 'STARBUCKS' in the DB table but user wants to search by 'Starbucks'. It works when user puts 'STARBUCKS' but does not when user puts 'Starbucks'. Is there a way we can pull values irrespective of case.Sandip Barnwal– Sandip Barnwal2025年06月05日 16:00:45 +00:00Commented Jun 5, 2025 at 16:00
-
What is the User Interface and what is between the User Interface and the CDS View?Sandra Rossi– Sandra Rossi2025年06月05日 18:07:57 +00:00Commented Jun 5, 2025 at 18:07
-
I have created a SQ01 report using this CDS view, users are using the SQ01 report to display the data.Sandip Barnwal– Sandip Barnwal2025年06月05日 19:41:32 +00:00Commented Jun 5, 2025 at 19:41
2 Answers 2
If you are on 7.51 or higher, you can use the sql command like VXLozano suggested.
Just create a new column in you cds and search on it or use the command in your query.
If you are on 7.50 or lower, you can achive it with a bit of a workaround.
First you need to create a domain with the same length as you current column and make you that lower case characters aren't allowed.
Afterwards you can create a datalement based on the domain.
Now you can cast your column to the new datalement.
cast( b.name1 as ZSD_NAME1_UPPER ) as EndUserName1_UPPER
The new column now contains all names in upper case.
To use this in a search you also need to convert your userinput in upper case.
There are some ways to achive this like the translate command or string template.
Hope it helps!
1 Comment
upper function is part of ABAP SQL and ABAP CDS since ABAP 7.51. The function to_upper is related neither to SQL nor to CDS, it's a pure ABAP function (since ABAP 7.02).I used the below syntax, and it seems to be working.
define view ZCDSV_XXX_EUGROUP
as select from /XXX/A as a
inner join /XXX/B as b
on a.epkunnr = b.epkunnr
{
key a.epkunnr as EUNumber,
upper(b.name1) as EndUserName1,
upper(b.name2) as EndUserName2,
b.street as Address,
b.str_suppl1 as AddressStreet2,
b.city1 as City,
b.region as StateProvince,
b.post_code1 as PostalCode,
cast(max( case a.atinn when '0000001361' then atwrt end ) as abap.char(4)) as EndUserGroup,
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