Thanks for the solution.
Sorry for my ignorance and If you dont mind could you please explain the solution provided by you or please point to the appropriate manual.
WITH cte(grpcol,pd) AS (
SELECT grpcol, PERIOD(from_dt,to_dt)
FROM date_range_test )
FROM TABLE ( TD_SYSFNLIB.TD_NORMALIZE_OVERLAP_MEET(NEW VARIANT_TYPE(cte.grpcol),
RETURNS (grpcol INT, pd PERIOD(DATE), cnt INT) HASH BY grpcol LOCAL
ORDER BY grpcol, pd ) AS dt
ORDER BY 1,2;
You'll find the function in the "Functions & Operators" manual, but it's not really "well documented".
Those functions only work for PERIODs and need a WITH to provide the source data, which is then passed to the table function.
The grouping column(s) are passed in the NEW VARIANT_TYPE and must be listed as first column(s) in RETURNS.
The PERIOD is the 2nd parameter for input and ouput and there's an optional count column returning the number of rows combined into one.
HASH BY must be the grouping column(s) and order by must be the grouping column(s) plus the period.
When you miss any part of that syntax you'll get a nice error message :-)