Conversion Functions in Input Parameters in HANA for filtering data

[upamanyu_mukherjee]

Hi ,

I have a scenario where data in my model needs to be filtered using an Input Parameter on a date field.The data type of field is NVARCHAR(8) and in the format '20170101'.The user will be providing input in the format '2017-01-01' using an Input Parameter having semantics date.Can this be handled using External to Internal Conversion Functions?

I have created a scalar function which converts the format from '2017-01-01' to '20170101' and have then assigned this function to the input parameter.However this doesn't seem to work as the data is not filtered as per expectation.

I have tried both with and without the Preserve Order option.

Function logic-

FUNCTION "Schema"."Pkg::ZSF_SQLDATE_TO_DATS" ( VIP_DATE NVARCHAR(10)) RETURNS CONV_DATE NVARCHAR(10)

LANGUAGE SQLSCRIPT SQL SECURITY INVOKER

AS

BEGIN select to_dats(:VIP_DATE) into CONV_DATE from dummy;

END;

Am I missing something or is my understanding completely wrong!?

FYI - I am currently using HANA 2.1

Thanks!!!

Upamanyu

input-parameter.png

[pfefferf]

The ExternalToInternal and InternalToExternal conversion functions are used (or better said) should be used by external client tools.

For your case I would add an additional input parameter of type "Derived from procedure/scalar function". Then you can use your function for that new parameter. The original parameter has to be mapped to the input parameter of the table function in the mapping dialogue.

In the filter expression you have to use the new parameter, because it will contain the converted parameter value.

Regards,
Florian