Use table field or column as a parameter in scripted calculation view

[shuqair_samer]

I have a procedure like the following in SQL:

select Code, Name,U_LineID, U_empID, U_Status, CASE WHENFromDate < @StartDate THEN @StartDate ELSE FromDate END ASFromDate,

CASE WHEN ToDate IS NULL THEN @EndDate

       WHEN ToDate > @EndDate THEN @EndDate

       ELSE ToDate END AS ToDate, U_Position, U_Project,U_Sponsorship

from

(

select Code, Name,U_LineID, U_empID, U_Status, U_Date ASFromDate,

      DATEADD(DAY,-1,(select top 1 history.U_Date AS ToDatefrom [@EMP_HISTORY] history

            where history.U_Date > [@EMP_HISTORY].U_Date

            and history.U_empID = [@EMP_HISTORY].U_empIDorder by history.U_Date)) AS ToDate,

            U_Position, U_Project, U_Sponsorship

from [@EMP_HISTORY]

where U_empID = @EmpID

) InnerQuery

WHERE (FromDate between @StartDate and @EndDate OR ToDatebetween @StartDate and @EndDate)

ORDER BY FromDate

When I try to convert it to normal view or calculation view in HANA, I face the issue that TOP keyword in the following nested query is not supported in HANA: (feature not supported to have TOP in the nested query) - I used LIMIT also but it does not solve the issue

,(select top 1 history.U_Date AS ToDate from [@EMP_HISTORY] history

            where history.U_Date > [@EMP_HISTORY].U_Date

            and history.U_empID = [@EMP_HISTORY].U_empIDorder by history.U_Date)

So I take the nested query and convert it to calculation view with two parameters:U_Date and EmpID like the following:

First Calculation View: (ABS_CA_EMPLOYMENT_HISTORY)

/********* Begin Procedure Script ************/

BEGIN

var_out = SELECT ADD_DAYS((select TOP 1 history."U_Date" AS"ToDate"

from "@EMP_HISTORY" history

            where history."U_Date" > :U_Date

            and history."U_empID" = :EmpID order byhistory."U_Date"),-1) AS "ToDate" FROM DUMMY

            ;

END /********* End Procedure Script ************/

Then I create another calculation view that make use of the above calculation view like the following:

Second Calculation View: (ABS_CA_EMPLOYMENT_HISTORY_DETAILS)

/********* Begin Procedure Script ************/

BEGIN

       var_out = SELECT "Code", "Name","U_LineID","U_empID", "U_Status", "U_Date",

      (SELECT "ToDate" FROM"_SYS_BIC"."Payroll/ABS_CA_EMPLOYMENT_HISTORY"

      (PLACEHOLDER."$$empid$$" => :EmpID,

      PLACEHOLDER."$$u_date$$" => "U_Date")) AS "ToDate",

"U_Position", "U_Project", "U_Sponsorship", "U_Remarks"

FROM "@EMP_HISTORY" ;

END /********* End Procedure Script ************/

I face another issue: feature not supported to have alias or table field ("U_Date") as input parameter to the calculation view.

is there a solution for that or workaround?

Your urgent reply is highly appreciated.

Thanks

[lbreddemann]

I branched this question to it's own thread - please don't "highjack" discussions

- Lars