topic Re: CDS View - Coalesce and case statement not working in Application Development and Automation Discussions

CDS View - Coalesce and case statement not working

Former Member — Sun, 28 Feb 2021 08:14:49 GMT

Hi Experts,

I have developed a CDS view and one of the column in it is of Bill of Lading(BOLNR).

My requirement is to put '#' if BOLNR is initial. I tried with Coalesce and Case Statement with below code but it is not working as expected.

Code1 - coalesce( _likp.bolnr, '#' ) as bolnr,

Code2- cast ( case when _likp.bolnr = ' ' then '#' else _likp.bolnr end as bolnr) as bolnr.

Is there any other away to achieve the above requirement in CDS View?

Re: CDS View - Coalesce and case statement not working

BiberM — Sun, 28 Feb 2021 09:32:15 GMT

Coalesce only works if the value is null, which is currently only possible within a Join.

The Case should work through. I'm currently only at the Phone but is there something like "initial" or is that value not an empty string? In CDS there is much less automatik data type conversion which is also valid for constants.

Re: CDS View - Coalesce and case statement not working

MateuszAdamus — Sun, 28 Feb 2021 09:37:52 GMT

Hello,

What is the error message you get?

Or is it just not producing the expected results?

Kind regards,

Mateusz

Re: CDS View - Coalesce and case statement not working

maheshpalavalli — Sun, 28 Feb 2021 12:33:37 GMT

This works right?

cast ( case when likp.bolnr = ' ' then '#' else likp.bolnr end as bolnr ) as test1

or if it is an older version, this also works, you don't need cast.

case when likp.bolnr = '' then '#' else likp.bolnr end as test

and tell us what is not working as well.

Re: CDS View - Coalesce and case statement not working

Former Member — Mon, 01 Mar 2021 08:26:41 GMT

Hi All,

the issues with Coalesce and Case is, for some records which are blank, it is working as expected. But some it is showing blank.

Is it because BOLNR is CHAR35 and having Numeric as well as Alphabetical records?

Re: CDS View - Coalesce and case statement not working

Former Member — Mon, 01 Mar 2021 08:31:32 GMT

Hi Mahesh,

By using Case or Coalesce, it is working for some records, but not for all records. Please see below screen shots:

Re: CDS View - Coalesce and case statement not working

Former Member — Mon, 01 Mar 2021 08:39:42 GMT

Hi All,

For Some records if you use either a case or coalesce it is working but for some records its not working.blank-bolnr.png non-blank-bolnr.png

Please refer Attachments for more clarification on the issue.