topic Re: ABAP CDS w/#COUNT_DISTINCT Problems (Null or # Value) in Application Development and Automation Discussions

ABAP CDS w/#COUNT_DISTINCT Problems (Null or # Value)

sap_cohort — Thu, 02 Feb 2023 02:55:42 GMT

I have an ABAP CDS View field value defined as follows:
The field definition is in an ABAP CDS Consumption View with Data Category #CUBE

@EndUserText.label: 'Related Link Count'
@Aggregation.default: #COUNT_DISTINCT
@Aggregation.referenceElement: ['RelatedLinkID']
cast(1 as abap.int4) as RelatedLinkCount,

The CDS Query referencing this #CUBE field is returning a value of 11. I should only get 10.
The plus one is because some of the lines have a field value of '#'.
The '#' is getting is being picked up as one of the distinct values
I'm not sure if in ABAP CDS if '#' really means NULL or Initial Value. I see the '#' in RSRT results.
I am thinking the '#' means NULL because the '#' value is generated from a CASE Statement without an ELSE.
Or does the null value '#' from the CASE result get interpreted to an ABAP INITIAL Value and just shows '#'?
The reason I want a null value is supposedly #COUNT_DISTINCT does not consider NULL Values but this does not seem to be the case for ABAP CDS. There are not any good examples of #COUNT_DISTINCT.
I'm sure ABAP CDS #COUNT_DISTINCT works fine if there are no null values.

Questions:
Is there a way to force a ABAP CDS field value to NULL?

Is #COUNT_DISTINCT in ABAP CDS really supposed to ignore NULL Values or is it just not working?

How could I have my field #COUNT_DISTINCT Annotations ignore the '#' values? (Not include # in my count)
(Should the solution be in the CDS Query instead?)

Thanks for any support on this!

Re: ABAP CDS w/#COUNT_DISTINCT Problems (Null or # Value)

Marian_Zeis — Thu, 02 Feb 2023 06:55:31 GMT

NULL is a distinct value, so it is counted on COUNT_DISTINCT

What about a case statement?

cast(
case when Postcode is initial then 0
      else 1
      end as abap.int4)          as RelatedLinkCount,

Re: ABAP CDS w/#COUNT_DISTINCT Problems (Null or # Value)

sap_cohort — Thu, 02 Feb 2023 18:57:48 GMT

Hi Marian, Thanks for your input and suggestion.

It seems that the case statement has no effect to exclude the "#".
Any other thoughts or suggestions! Could really use this count to exclude "#" somehow.

Anyone know what we could do to exclude the "#" value from #COUNT_DISTINCT in ABAP CDS?

Greatly appreciated!

Re: ABAP CDS w/#COUNT_DISTINCT Problems (Null or # Value)

Marian_Zeis — Fri, 03 Feb 2023 06:50:13 GMT

Hi kenneth.murray2

i don´t know, if its a actually "'#", maybe try:

cast(
case when Postcode is initial then 0
     when Postcode = '#' then 0
      else 1
      end as abap.int4)          as RelatedLinkCount,

Re: ABAP CDS w/#COUNT_DISTINCT Problems (Null or # Value)

sap_cohort — Fri, 03 Feb 2023 14:14:50 GMT

Hi Team Community, Does anyone have any other thoughts on how we could acheive this result?

Basically looking for a way to use #COUNT_DISTINCT but, exclude a specific value (Null or any other value)
Thank You!

Re: ABAP CDS w/#COUNT_DISTINCT Problems (Null or # Value)

sap_cohort — Fri, 03 Feb 2023 15:52:11 GMT

Thanks for the follow-up... !!!

I did try all the different ways like '#', '', null etc.

It totally looks like the CASE has no affect on the #COUNT_DISTINCT annotation uggg.

I'm still working on this so if you or anyone has any ideas how to accomplish this it would be greatly helpful!

Ultimately the solution should have the #COUNT_DISTINCT ignore null values or somehow decrement 1 from the final count if any null values are in the list.

Thanks for any input!

Re: ABAP CDS w/#COUNT_DISTINCT Problems (Null or # Value)

sap_cohort — Wed, 24 May 2023 21:08:48 GMT

Wanted to close this out.

Solution was to have a separate indicator field if any of the RelatedLinkID was null.
This indicator could be used further down the road to subtract 1 from the count. Hope that helps somone.