Skip to Content
0
Former Member
Aug 16, 2016 at 01:48 PM

How does COUNT work/defined

39 Views

It seems that COUNT takes a complete table column of values and returns the number of elements. I need to craft a sql procedure, which, takes a table column of values, which returns a VARCHAR. The issue i'm running into is with the definition of the function. It seems, that in order to take a table as input, the function must also return a table.

consider table:

mytable:

first last uri

----- -------------- ------------------

andy smith my.pkg.sap

andy smith your.pkg.sap

andy smith this.pkg.com

fred jones pkg.something.core

fred jones my.pkg.sap

testing count on a cartesian table with procedure:

drop procedure test_counter;

create procedure test_counter() language SQLSCRIPT as

begin

declare cnt,sze integer;

declare uri_size,uri_cnt INTEGER;

declare str VARCHAR(5000);

declare str_num NVARCHAR(1023);

declare uri_1,uri_2,uri_lnk VARCHAR(200);

declare lt_lnk_uri TT_STRINGS;

lt_first = select distinct("FIRST") as "FIRST_1" from test_count;

lt_last = select distinct("LAST") as "LAST_1" from test_count;

lt_cartesian = select first_1,last_1 from :lt_first, :lt_last;

select first_1,last_1,count(pkg) from test_count,:lt_cartesian

where "FIRST" = first_1 and "LAST" = last_1 group by grouping sets ((first_1,last_1));

end;

call test_counter();

correctly returns distinct first,last combinations with their counts as I wanted.

first_1 last_1 count(pkg)

----- ---------- -------------------------

1 andy smith 3

2 fred jones 2


i need something like: select first,last,makestr(mytable.pkg) from myschema.mytable;

this would ideally return:

first last str

----- -------------- -----------------------------------------------

1 andy smith my.pkg.sap, you.pkg.sap, this.pkg.com

2 fred jones pkg.something.core, my.pkg.sap

How can I define the function signature to do this?