Distinct Count in Script Calculation View

[former_member186082]

Hi All,

Lets consider we have two tables as this.

TABLE1

TABLE2

A script calculation view on these tables is created with below code.

SELECT T1.T1C1, T1.T1C2, T2.T2C1, T2.T2C2,

COUNT(DISTINCT T1.T1C1) AS DIST_COUNT

FROM TABLE1 T1 LEFT OUTER JOIN TABLE2 T2 ON T1.T1C1= T2.T2C1

GROUP BY T1.T1C1, T1.T1C2, T2.T2C1, T2.T2C2;

If I query for

SELECT T1C2, COUNT(DIST_COUNT)
FROM "_SYS_BIC"."MYPKG/CA_VIEW"

GROUP BY T1C2;

Results are:

Results should have been:

Here are my questions:

1. Is this behavior expected in HANA Script view?

2. How can we achieve this in script view? Graphical works for this, creating a counter.

Thanks,

Chandra.

[pfefferf · Accepted solution]

Hello Chandra,

as you did not share what you really wanna reach from a business point of view following is only pure technical related. After thinking about your described example I really had little knot in my head, cause I do not understand why the things are done like they are done :-). But back to the content.

First, the result which is produced by the select is correct from a technical point of view. As you are using a "count(distinct)" in your first select on column T1C1 you get for every row a result of 1 in that column for every result of the join.

The 1 is not really relevant for the result you get, but just for your knowlege. As you are doing a count on the column (valid for all other columns) in the second select grouped on column T1C2 you get simply the number of lines per distinct value in T1C2. And this is the result you get which is fine.

From a techical point of view you are trying do determine the number of distinct values in T1C1 assigned to a T1C2 value. To reach that with your way, you should remove the "count(distinct)" in your first select and do the "count(distinct)" in your second select. Following anonymous block coding

do
begin
  lt_t1 =           select 1 as t1c1, 1 as t1c2 from dummy
          union all select 2 as t1c1, 1 as t1c2 from dummy
          union all select 3 as t1c1, 2 as t1c2 from dummy
          union all select 4 as t1c1, 2 as t1c2 from dummy
          union all select 5 as t1c1, 3 as t1c2 from dummy;

  lt_t2 =           select 1 as t2c1, 'A1' as t2c2, 1 as t2c3 from dummy
          union all select 1 as t2c1, 'A2' as t2c2, 2 as t2c3 from dummy
          union all select 1 as t2c1, 'A3' as t2c2, 3 as t2c3 from dummy
          union all select 2 as t2c1, 'A1' as t2c2, 1 as t2c3 from dummy
          union all select 2 as t2c1, 'A2' as t2c2, 2 as t2c3 from dummy
          union all select 3 as t2c1, 'A3' as t2c2, 1 as t2c3 from dummy
          union all select 4 as t2c1, 'A4' as t2c2, 1 as t2c3 from dummy
          union all select 4 as t2c1, 'A2' as t2c2, 2 as t2c3 from dummy
          union all select 5 as t2c1, 'A1' as t2c2, 1 as t2c3 from dummy
          union all select 5 as t2c1, 'A3' as t2c2, 2 as t2c3 from dummy;
          
  lt_join = SELECT T1.T1C1, T1.T1C2, T2.T2C1, T2.T2C2
            FROM :lt_t1 as t1 LEFT OUTER JOIN :lt_t2 as T2 ON T1.T1C1= T2.T2C1
            GROUP BY T1.T1C1, T1.T1C2, T2.T2C1, T2.T2C2;
   
  select t1c2, count(distinct t1c1) 
  from :lt_join
  group by t1c2;
end;

provides following result:

And even more simpler you can produce the same result just using TABLE1, cause the relevant information comes all from TABLE1. Necessary information from TABLE2 can be joined if required of course:

do
begin
  lt_t1 =           select 1 as t1c1, 1 as t1c2 from dummy
          union all select 2 as t1c1, 1 as t1c2 from dummy
          union all select 3 as t1c1, 2 as t1c2 from dummy
          union all select 4 as t1c1, 2 as t1c2 from dummy
          union all select 5 as t1c1, 3 as t1c2 from dummy;
   
  select t1c2, count(t1c1) 
  from :lt_t1
  group by t1c2;
end;

Regards,
Florian

[former_member186082 · Accepted solution]

Hi Florian,

Thanks for the quick reply.

I cannot use Table1 alone as my other reports depend on Table2 as well. I need a single model on Table1 and Table2.

My requirement is something like this - Lumira report with DISTINCT COUNT of VBELN from VBAK. This distinct count can be analyzed with respect to any attribute - VKORG/VTWEG/SPART/VKGRP etc. I've some analysis to be made from VBAP as well, so the same model should have a join with VBAP.

When we join VBAK and VBAP and create distinct count of VBELN in HANA(SQL Script view) and then analyze from Lumira, CE considers all the joined rows also for grouping. If we have the same solution in graphical view, counter works on VBELN.

I feel that when we run distinct count of VBELN by VKORG (from lumira) on top of script view, join executes first which involves joined columns and columns from table 2 also for grouping then displays count which is incorrect. Whereas counter in graphical view don't take join or columns from table 2 for grouping.

I want to confirm if my understanding about engine execution is right?

Regards,

Chandra.