116 - ABAP on HANA - CDS View - Aggregate Functions Part2

Added: 2026-06-03

[00:00:02]Hello everyone. We will continue that topic about on Hana and currently we are covering CDS view and in the last video we started with the aggregate functions into the CDS view. We took one requirement based upon that requirement, we created the CDS view.

[00:00:26]Now into this particular video, firstly we will check the output of this particular CDS view.

[00:00:35]I will run this CDS view.

[00:00:39]Now I will provide that date as that input.

[00:00:44]I will go for same to same date.

[00:00:48]You can take any test case of your system, yes.

[00:00:52]I will go for open data preview. We need to pass that date in YYYYMMDD format.

[00:01:02]Now if I will check the result 97159 euro, yes.

[00:01:09]1 lakh 10,000, yes. GBP, yes.

[00:01:14]240 INR, 1,000 USD. And we got the same to same result. And have you seen how performance effective it is? We all know this CDS view runs on that database layer. From that database layer itself, we are getting the amount sum of this particular NETWR column. We have not written anything or we have not written any logic on the application layer to make the sum of this NETWR column. From that database itself, it is making that sum and we are getting that result onto that application layer. Now I will go for the next part. I will simply create a program and I will use this CDS view there.

[00:02:05]I will go to SC38 transaction code and I will create a program.

[00:02:11]This is our 11th number program. Yes.

[00:02:16]I will go for create.

[00:02:20]I will give that description.

[00:02:24]To make some of amount based upon creation date.

[00:02:34]I will choose that type as executable program.

[00:02:39]I'll go for package.

[00:02:46]I'll stimulate.

[00:02:49]Yes.

[00:02:51]Now, how I will take a input into this particular program through parameters.

[00:02:58]Here, intentionally, I will take a different name so that I can show you this is program parameter name and this is CDS view parameter name.

[00:03:09]P underscore date type ERDAT.

[00:03:13]I will provide the selection text.

[00:03:23]And then I will write a query for that particular CDS view.

[00:03:29]Go to text elements.

[00:03:34]DDIC reference.

[00:03:37]This is coming from data element.

[00:03:41]Now, I will write a query.

[00:03:45]Select star from ZCDS view 14 demo.

[00:03:54]Now I will pass. What is our CDS view parameter name?

[00:03:58]P underscore ERDAT is equal to What is our program parameter? P underscore date. You all know all host variables must be prefixed with at direct.

[00:04:13]Now I will write into table at the rate data.

[00:04:22]Now I will go for LT underscore date.

[00:04:28]Now I will simply go for the class, the demo class.

[00:04:33]Pattern a back object patterns.

[00:04:39]Now I will choose the method display.

[00:04:47]Now I will use LT underscore date.

[00:05:01]So whatever the input we will pass to the program, that will go to the parameter of this CDS view. It will fetch that data from VBAK table, yes.

[00:05:13]And we will get that data into this internal table and we are displaying this internal table.

[00:05:23]I'll pass the same to same date here.

[00:05:27]And you can see we got the same to same result. Now we will go for another part of the requirement. How we can go for filtration here? So as of now, yes, we have simply made the sum, we have understood the group by clause. Now anyone can ask the question in the interview. So, have you applied the filtration in the CDS view? Suppose, if you want to filter some data based upon some condition, yes? So, whenever you want to filter that data, we'll simply filter using the having clause. We'll simply use the having clause. So, I will take a requirement first. Let's see as of now what we are getting. The output which we are getting 97159, 110000, means 110,000, 241,000.

[00:06:25]But, customer is saying I just want to see only those records where the sum is greater than 1,000. If the sum is less than 10,000 If the sum is less than 1,000, it does not make any sense for me. Whenever the amount is greater than greater than 1,000, I only want to see that particular result, yes?

[00:06:52]Currency does not matter, yes? One Suppose, greater than 1,000, it can be 1,001 GBP, it can be 1,001 EUR, 1,001 INR, 1,001 USD. But, customer want that 1,000 greater than 1,000 record customer want in the output. Where the sum of the amount is greater than 1,000. So, we'll simply see how we can filter based upon that.

[00:07:22]So, what I will write? I will simply simply go for having clause here.

[00:07:28]So, I will write having.

[00:07:31]Now. Now, which particular thing you want to filter? Where sum of NETWR column is greater than 1,000.

[00:07:44]So, this is the way how you can go for filtration if someone ask in the interview, yes, have you filtered, yes, that data whatever is in the CDS view?

[00:07:56]So, yes, we can go for filtration or we can apply the filtering using the having clause. Now, I will go for syntax check.

[00:08:08]I will simply go to activation and we'll see. Now, previously we are getting four records in the output. Now, we will only only get that two records.

[00:08:20]So, I will run.

[00:08:22]Run as Now, I will go for date.

[00:08:29]Suppose date is 2024 02 18.

[00:08:38]Yes, now you can see other two records are not coming, yes. Now, if I'll check that program also, this is our program.

[00:08:46]If whenever I will run Now, you can see. So, what is the summary of this particular video? In this video, firstly, I run the CDS view, we are getting the result. And the best part is sum is calculating on the database layer and we are getting the result here. So, performance effective, yes.

[00:09:10]After that, I created the program. In that program, we simply call that CDS view, we got the expected result. After that, we covered, yes, if I want to apply some filtration into the result, so how I can apply? So, you can apply the filtering through having clause. So, I have written having where sum of NETWR is greater than 1,000. I passed that condition and we have got only the two results in the output.

[00:09:43]So, that's it in this video. Thank you.