Skip to Content

Schema Mapping in table function for WORKDAYS_BETWEEN() parameters

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Sep 27, 2017 at 02:49 PM

    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 !

    Add comment
    10|10000 characters needed characters exceeded