topic Call UDF from CDS view and SQL console in Application Development and Automation Discussions

Call UDF from CDS view and SQL console

rafal_matuszewski2 — Fri, 11 Dec 2015 15:20:14 GMT

Hi Experts,

I've question about user defined functions UDF - scalar type.

In my CDS view I tried to use currency_conversion function - with success.


@AbapCatalog.sqlViewName: 'Z079MM_CV_CDS_07'
@EndUserText.label: 'MBEW'
define view Z079mm_Cv_Mebew_Z_Ext
with parameters p_to_currency:abap.cuky(5),
                               p_exc_date:abap.dats,
as
select from mbew    
       join t001k
         on mbew.bwkey = t001k.bwkey
       join t001
         on t001k.bukrs = t001.bukrs    
{
  key mbew.bwkey,
  key mbew.matnr,
  key mbew.bwtar,
  lbkum,
  salk3,
  :p_to_currency as currency,

/////----------->here
currency_conversion( amount => salk3,
                       source_currency => waers,
                       round => 'X',
                       target_currency => :p_to_currency,
                       exchange_rate_date => :p_exc_date
                       ) as salk3_cur               

                                                                                                                                                          
}

This function is defined by SAP in schema SAPABAP1:

Later I wanted to use my function DIV_CURR defined in schema EXRMATUSZEWSKI:


@AbapCatalog.sqlViewName: 'Z079MM_CV_CDS_09'
@EndUserText.label: 'MBEW'
define view Z079mm_Cv_Mebew_Z_Ext2
with parameters p_to_currency:abap.cuky(5),
                p_exc_date:abap.dats
as
select from mbew    
       join t001k
         on mbew.bwkey = t001k.bwkey
       join t001
         on t001k.bukrs = t001.bukrs    
{
  key mbew.bwkey,
  key mbew.matnr,
  key mbew.bwtar,
  lbkum,
  salk3,
  :p_to_currency as currency,

currency_conversion( amount => salk3,
                       source_currency => waers,
                       round => 'X',
                       target_currency => :p_to_currency,
                       exchange_rate_date => :p_exc_date
                       ) as salk3_cur,
/////----------->here                      
  div_curr(salk3,2) as result2                             
  //"EXRMATUSZEWSKI"."DIV_CURR"(salk3,2) as result3                                                                                                                                                    
}
}fffd

but compilator says "Function div_curr is unknown". I tried to add schema name but it not worked.

Both functions are scalar functions and are defined in similar way:

What is wrong with my code in CDS view? Is my own function really unreachable from CDS view?

In contrast in SQL console:

this works:


select div_curr(10,2) as result
from dummy

this is not working:


select currency_conversion( amount => '1000',
                           source_currency => 'PLN',
                           rounding => 'X',
                           target_currency => 'EUR',
                           exchange_rate_date => '20151211'  ) as salk3_cur
  from dummy


select "SAPABAP1"."CURRENCY_CONVERSION"( amount => '1000',
                                         source_currency => 'PLN',
                                         rounding => 'X',
                                         target_currency => 'EUR',
                                         exchange_rate_date => '20151211'  ) as salk3_cur
  from dummy

Again why? what's the difference between those functions?

Best regards

Rafal

Re: Call UDF from CDS view and SQL console

christian_seitel — Fri, 11 Dec 2015 16:11:46 GMT

Hi,

in ABAP CDS you can use only built-in functions which are listed in the documentation

(section ABAP CDS - SELECT, Predefined Functions).

It is not clear which database function is then actually used. E.g. on HDB there is also a function "CONVERT_CURRENCY"

In the CDS view there is context menu item "Show SQL create statement". There you can search for the actual function used.

The functions are not dynamically mapped. You cannot use any DB-function.

Re: Call UDF from CDS view and SQL console

rafal_matuszewski2 — Fri, 11 Dec 2015 21:17:59 GMT

Dear Christian,

I'm working on SAP HANA SP11.

I checked function CONVERT_CURRENCY but in CDS view I got erorr 'Function is unknown'.

In SQL console it works:

SELECT CONVERT_CURRENCY(amount=>'1000.0',

"SOURCE_UNIT_COLUMN" =>'PLN',

"SCHEMA" => 'SAPABAP1',

"TARGET_UNIT_COLUMN" => 'EUR',

"REFERENCE_DATE" =>'2015-12-11',

"ERROR_HANDLING"=>'set to null',

"CLIENT" => '200') as converted

FROM dummy

Is it possible to use built-in SQL functions in CDS view?

Here I found list of SQL functions:

SQL Functions - SAP HANA SQL and System Views Reference - SAP Library

Could you give me link to CDS-predefined functions:


(section ABAP CDS - SELECT, Predefined Functions).

Best regards

Rafal

Re: Call UDF from CDS view and SQL console

rafal_matuszewski2 — Mon, 14 Dec 2015 08:50:05 GMT

I think I found answer for my questions: https://scn.sap.com/thread/3683265

Jasmin Gruschke:explained:

"the list of provided features can be found in the ABAP Language Documentation (F1 in the CDS View). As CDS in ABAP is abstracted from the database layer, the database features are not directly accessible."

Best regards