Call UDF from CDS view and SQL console

[rafal_matuszewski2]

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

[rafal_matuszewski2]

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

[christian_seitel]

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.

[rafal_matuszewski2]

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

[rafal_matuszewski2]

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