HANA Table function Loop records and append

[former_member543694]

Hi Experts,

Please help me with how to loop the records and append new records to the output table in table function. I want to use this table function in Calculation view thats why not going with procedure.

My Requirement : I need to generate duplicate records for each record in my source table .

Each record has 2 dates - document date and clearing date.

1. I need to create a additional date field( zdate) and populate it.
2. I need to generate duplicate records for the number of days between clearing date and document date .
Eg : If document date is Jan 1 and Clearing date is Jan 28 then I need to generate 27 records including the original record.
Eg.
Original record - 
Record   DocNumber  DocumentDate  ClearningDate
Record1  100000020  20181110      20181113
Record2  100000021  20181111      20181113
Required output - 
Record  DocNumber   DocumentDate  CustomDate   ClearningDate
Record1 100000020   20181110      20181110
Record2 100000020   20181110      20181111
Record3 100000020   20181110      20181112
Record4 100000020   20181110      20181113      20181113
Record5 100000021   20181111      20181111
Record6 100000021   20181111      20181112
Record7 100000021   20181111      20181113      20181113 

So far I did like this and I am able to fetch the date and retun the data but not able to write code to generate duplicate records. Please help.

FUNCTION "FinanceT"."Test::ZF_Record_APPEND" ( ) 
	RETURNS table ("0COMP_CODE" NVARCHAR(10),
			 "0DEBITOR" NVARCHAR(10),
			"0AC_DOC_NO" NVARCHAR(10),
			"0DOC_DATE" NVARCHAR(8),
			"0CLEAR_DATE" NVARCHAR(8),
                        "ZDATE" NVARCHAR(8))
	
		LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER AS
BEGIN
/***************************** 
	Write your function logic
 *****************************/
 ZTEMP = 
    SELECT "0COMP_CODE",
		"0DEBITOR",
		"0AC_DOC_NO",
		"0DOC_DATE",
		"0CLEAR_DATE" 
		FROM 
"_SYS_BIC"."ACCTABLE" ;


  Return
   SELECT "0COMP_CODE",
		"0DEBITOR",
		"0AC_DOC_NO",
		"0DOC_DATE",
		"0CLEAR_DATE" 
		FROM :ZTEMP;


END;

[former_member302041]

select
 DOCNUMBER,
 DOCDATE, 
 DATE_SAP as CUSTOMDATE,
 case
  when CLEARINGDATE = DATE_SAP then CLEARINGDATE
  else ''
 end as CLEARINGDATE 
from "_SYS_BI"."M_TIME_DIMENSION"
join "_SYS_BIC"."ACCTABLE"
on DATE_SAP >= DOCDATE and DATE_SAP <= CLEARINGDATE
order by DOCNUMBER, DATE_SAP