Skip to Content

Conversion Functions in Input Parameters in HANA for filtering data

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Jul 13, 2017 at 03:02 PM

    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

    Add comment
    10|10000 characters needed characters exceeded