Skip to Content
0

HANA Modeling: Table function doesn't return SECONDDATE type field

Jun 12, 2017 at 08:37 AM

412

avatar image

Dear SCN Members,

I have created a Table Function (TURNI_FERMATE) using this sqlscript as follows:

FUNCTION "BIUSER"."bi.sap.mes::TURNI_FERMATE" ( ) 
    RETURNS TABLE ("HANDLE_ID" NVARCHAR(412), "SITE" NVARCHAR(6), "RESRCE" NVARCHAR(36), "DATE_TIME" SECONDDATE, "TURNO" NVARCHAR(15))
    LANGUAGE SQLSCRIPT
    SQL SECURITY INVOKER AS
BEGIN
RETURN
SELECT DISTINCT 
          RLS.HANDLE AS HANDLE_ID,
          RLS.SITE,
          RES.RESRCE,
          RLS.DATE_TIME,
          (SELECT CAL.TURNO FROM 
              "WIPUSER"."ZME_CYB_CALENDAR" CAL 
              WHERE 
                  CAL.SITE = RLS.SITE
                  AND CAL.ZMACCHINA = RES.RESRCE
                  AND RLS.DATE_TIME BETWEEN CAL.DATA_ORA_INIZIO AND CAL.DATA_ORA_FINE
          ) AS TURNO
      FROM "WIPUSER"."ZME_RESRCE_LOG_STATE" RLS 
      LEFT OUTER JOIN "WIPUSER"."RESRCE" RES
          ON RLS.HANDLE = RES.HANDLE;
END;

I have used this Table function to create a Calculation View (Data category: Dimension) as follows:

But in the result table the field "DATE_TIME" (data type SECONDDATE) is missed.

Using the same sqlscript to create a Calculation View type SQL Script (now deprecated) all works well.

It seems that the SECONDDATE data type is not recognized. The same situation happens also with SECONDTIME data type. With DATE data type it works.

Any suggestion?

The environment of HANA is 1.00.122.08.1490178281 (fa/hana1sp12)

Best regards,

Denis P.

parog.png (5.8 kB)
mm9wu.png (6.1 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Misaq T Jun 13, 2017 at 06:56 AM
0

Hi Utente,

As a HANA modeler, we must handle SECOND DATE in an exceptional way when it comes to loading the flat file or consuming table function in a calculation view.

Alter your code as below to change the data type as NVARCHAR.

FUNCTION"BIUSER"."bi.sap.mes::TURNI_FERMATE" ( ) 
    RETURNS TABLE("HANDLE_ID" NVARCHAR(412), "SITE" NVARCHAR(6), "RESRCE" NVARCHAR(36), "DATE_TIME" NVARCHAR(20), "TURNO" NVARCHAR(15))LANGUAGESQLSCRIPTSQL SECURITY INVOKER AS

Hope this helps!

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Thank you very much Misaq!

I followed your suggestion, then I convert the field from NVARCHAR to SECONDDATE (by using seconddate(stringarg) function) in the next step.

Best regards,

Denis P.

1
Utente Vimar Generico Utente Vimar Generico

Awesome :-)

0