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?