Input parameters not working in table function

[former_member184624]

Dear HANA Experts,

I have written the table function with 3 input parameters. I am passing 3 parameters to table function, but records are not filtering based on the input parameters. Can you please suggest, why these records are not filtering based on the input parameters. In select query, where condition is not working. I am passing 017 as a company code, but i am getting the output of all company code data.

FUNCTION "XXXX"."ZTI.YYY::TF_FA_AAAA" 
(IP_COMP_CODE VARCHAR(3),
IP_DAY_FROM DATE, IP_DAY_TO DATE)
RETURNS TABLE
(
WC_TRID1 VARCHAR(87),
SOURSYSTEM VARCHAR(2),
COMP_CODE VARCHAR(3),
"0CALDAY" VARCHAR(8),
NO_OF_PO DECIMAL(15,2)
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN
/*****************************
Write your function logic
*****************************/

--Calculation for No. PO (actual) RKF: WY_RKF0069_WRYCWR06 (Restrictions: KYF: WK_COUNT, WC_RTYFL: PO
--WC_PONO : Not Null, WC_VIR_PO : N and NULL.

NO_OF_PO = select "WC_TRID1", "0SOURSYSTEM" as SOURSYSTEM, "0COMP_CODE" AS COMP_CODE, "0CALDAY",
           count(WK_COUNT) OVER(PARTITION BY "0DIVISION","0SALESORG","0DISTR_CHAN","WC_SOLDTO",
           "WC_SRPNO") AS "NO_OF_PO"
           FROM "ZTI_VVVV"."AHX1B_WRYCWR06"
          WHERE "WC_RTYFL" = 'PO' AND "WC_PONO" IS NOT NULL AND "WC_VIR_PO" = 'N' OR "WC_VIR_PO" IS NULL AND
           "0COMP_CODE" = :IP_COMP_CODE AND "0CALDAY" BETWEEN :IP_DAY_FROM AND :IP_DAY_TO;    
RETURN
SELECT * FROM :NO_OF_PO;
END;

Calling the function 
SELECT * FROM 
"XXXX"."ZTI.YYY"::TF_FA_AAAA" ('017','20280101','20180605')

<br>

[venkateswaran_k · Accepted solution]

Hi

In your table function parameter - you declared as DATE.

Assuming "0CALDAY" is a DATE type in the table.

So please pass as follows:

SELECT * FROM 
"XXXX"."ZTI.YYY"::TF_FA_AAAA" ('017','2028-01-01','2018-06-05')

Regards,

[former_member184624 · Accepted solution]

Thank you for your reply.

In where condition, bracket is missing

"WC_RTYFL" = 'PO' AND "WC_PONO" IS NOT NULL AND ("WC_VIR_PO" = 'N' OR "WC_VIR_PO" IS NULL) AND 

"0COMP_CODE" = :IP_COMP_CODE AND "0CALDAY" BETWEEN :IP_DAY_FROM AND :IP_DAY_TO;

Problem solved.