Schema Mapping in table function for WORKDAYS_BETWEEN() parameters

[former_member184969]

Hi all.

It’s over Midnight… and I’m stuck with that problem. Time to ask for some help !

In a given table function we are using the WORKDAYS_BETWEEN() function, which rely on the TFACS calendar table from SAP ECC to calculate working days between 2 dates.

So of course you have to precise in the parameters what calendar code you want to use, and in what schema it is located.

So the problem is that the schema name ‘SAPDHL’ hardcoded there won’t survive the transport to the quality environment

I’ve already tried many things :

• 1)Set default schema to ‘xHL’ and put ‘xHL’ in function parameter
  ==> fails ! schema mapping is not effective : At execution, I get a (stupid) message saying xHL.TFACS can’t be found.
  (xHl being the generic schema mapping in our environment)

• 2)Set default schema to ‘xHL’ and omit the SCHEMA NAME in function parameter
  ==> fails ! by default , the function uses the Runtime schema; At execution, I get a stupid message saying [myuserId].TFACS can’t be found.

• 3)Use parameter ::CURRENT_OBJECT_SCHEMA instead ==>fails ! current Schema is _SYS_BIC; At execution, I get a stupid message saying _SYS_BIC.TFACS can’t be found.

• 4)Try to make use of [select PHYSICAL_SCHEMA from M_SCHEMA_NAME where AUTHORING_SCHEMA = ‘xHL’ ] directly in function call or by using a variable
  ==> fails. The function won’t accept it as a parameter (since it is a TABLE, not a string, I guess)

• 5)Build a synonym. After reading a bit about it, implemented “airbus.quality.1V80_SharQ_COT\XHLSynonym.hdbsynonym” with several attempts : SAPDHLà SAPDHL, _SYS_BICà xHL, xHLà SAPDHL
  ==> Fails, fails, fails. It seems my synonym is inefficient. Probably because WORKDAYS_BETWEEN() is ignoring synonyms…
• This is still the most elegant track I’ve followed. Maybe I badly implemented the thing.

The last and only solution I see is to let the function with ‘SAPDHL’ hardcoded (3 times in the TF), and have a ‘powerfull user’ change it directly in the target system. This is ugly. I don’t even know if it’s feasible. I want to avoid this.

Thanks for your support.

[Former Member]

Hello Stéphane!!!

May I suggest you to lightly change the header of your function by :

LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
DEFAULT SCHEMA SCHEMA_ALIAS_IN_MAPPING_TABLE
READS SQL DATA AS

BEGIN ..............................................

You'll find the schema mapping table and aliases in HANA Modeler options. No need for quotes or anything for the schema alias.

I hope it helps even before midnight 🙂 🙂 🙂

See you !

[former_member746766]

For ADD_WORKDAYS we used a variable as last argument in the function:

DECLARE V_SCHEMA CHAR(6);

SELECT physical_schema INTO V_SCHEMA FROM "_SYS_BI"."M_SCHEMA_MAPPING" WHERE authoring_schema = 'ABAP';