Table Function/Query error inside a “cross-database queries” scenario

former_member283197 — Tue, 19 Sep 2017 09:40:26 GMT

Dear,

I’m asking your support for a problem regarding a Table Function inside a “cross-database queries” scenario.

This is our Hana landscape.

This is the table function created in the BI Tentant HBQ.

FUNCTION "BIDEV"."dwh.mes.mes_tf::TF_TURNI_FERMATE" (IP_YEAR INTEGER) 


       RETURNS TABLE ("HANDLE_ID" NVARCHAR(412), "SITE" NVARCHAR(6), "RESRCE" NVARCHAR(36), "DATE_TIME" NVARCHAR(20), "TURNO" NVARCHAR(15), "DATA_ORA_INIZIO" NVARCHAR(20), "DATA_ORA_FINE" NVARCHAR(20))


       LANGUAGE SQLSCRIPT


       SQL SECURITY INVOKER AS


BEGIN


RETURN


SELECT DISTINCT 


              RLS.HANDLE AS HANDLE_ID,


              RLS.SITE,


              RES.RESRCE,


              RLS.DATE_TIME_END AS DATE_TIME,


              CAL.TURNO,


              CAL.DATA_ORA_INIZIO,


              CAL.DATA_ORA_FINE


        FROM HMQ.WIPUSER.ZME_RESRCE_LOG_STATE RLS 


        LEFT OUTER JOIN HMQ.WIPUSER.RESRCE RES


              ON RLS.HANDLE = RES.HANDLE


        LEFT OUTER JOIN HMQ.WIPUSER.ZME_CYB_CALENDAR CAL


           ON CAL.SITE = RLS.SITE 


           AND CAL.ZMACCHINA = RES.RESRCE


           AND RLS.DATE_TIME_END > CAL.DATA_ORA_INIZIO AND  RLS.DATE_TIME_END <= CAL.DATA_ORA_FINE


        WHERE YEAR(RLS.DATE_TIME_END) = :IP_YEAR;


END;

I have used this Table Function inside a Calculation View always, into the BI Tenant HBQ.

If I try to get a preview data, this error appears.

The same Table Function, created directly in the MES Tentant HMQ, works well.

So, I tried to execute the core selection query of the table function directly into SQL Console, but the error persists.

By excecuting queries on single tables, all correctly work.

Below the structures of tables of the MES Tenant HMQ used by Table Function.

The environment of HANA is SPS 12 (1.00.122.08.1490178281)

Any suggestion?

Best regards,

Denis Peretto.

Re: Table Function/Query error inside a “cross-database queries” scenario

former_member283197 — Fri, 22 Dec 2017 14:28:15 GMT

Solved by SAP Support.

This is the response:

This query error is identified as known issue for accessing temporary index with cross database access environment. And the bug was fixed in reversion 122.09

[Workaround]
Add "WITH HINT (NO_CS_JOIN)" at the end of the problematic query/select statement. Please note, using this Workaround can Impact performance.

Question Re: Table Function/Query error inside a “cross-database queries” scenario in Technology Q&A

Table Function/Query error inside a “cross-database queries” scenario

Re: Table Function/Query error inside a “cross-database queries” scenario