on 01-19-2016 9:27 AM
Hi,
When using the built-in function instr() in an ABAP CDS view I face the issue that specifying a character literal that could also be a numeric literal result in a compilation error: "Funktion INSTR: Parameter an Position 2 hat den falschen Datentyp NUMC" Translated into english it would be like 'function INSTR: parameter at position 2 has the wrong data type NUMC'. The CDS view is defined as follows:
@AbapCatalog.sqlViewName: 'z_test_jg3'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'test 2'
define view Z_Test3 as select from vbak {
//Key
key vbak.vbeln as SalesDocument,
instr(vbak.vbeln, '50') as test
}
As you can see literal '50' is used. If I change the literal to e.g. ' 50' or 'a50' is error is gone (of course). However, field vbeln of table vbak does only contain numeric values. Thus, the intention of this statement is to check if the vblen contains string '50'. Is there any possibility to either indicate that '50' should be interpreted as character literal? Or is there any other possibility or work-around?
Best Regards,
Johannes
An easy way to bypass this issue is to use the following definition instead:
@AbapCatalog.sqlViewName: 'z_test_jg3'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'test 2'
define view Z_Test3 as select from vbak {
//Key
key vbak.vbeln as SalesDocument,
instr(replace(vbak.vbeln, '0', 'a'), '5a') as test
}
Via this chained function usage at first zeros are replaced by some single, non-numeric character (you could also replace another number value). Secondly, function instr() could be used as initially intended. Note: Function replace() will need additional runtime. However, as I just needed to get this working and my code is neither performance ciritial nor there are other backdraws for my use-case, this is a fine solution for me.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Johannes
Can you try assigning '50' to a variable type NVARCHAR and then use that variable in instr function ?
Something like., lv := '50' ;
instr(vbak.vbeln, lv)
Cheers
Anindya
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I saw videos for CDS with Input Parameters, did not test myself through .
How to create ABAP CDS Views with Input Parameters on SAP HANA - YouTube
Easier way could be using a SUBSTRING function to convert '50' to character format before using in Instr function.
Like, Lars I did not get INSTR function , may be a version problem. I tried that with Replace function and it worked.
Here is my code :
**********************************************************************************
@AbapCatalog.sqlViewName: 'ZSQL_VIEW'
define view ZCDS_TEST
as select from /bic/azbsdoit100 as vbak
{
key vbak.doc_number as SALES_DOC ,
REPLACE (vbak.doc_number,'60', SUBSTRING ('50',1,2)) as test
}
*********************************************************************************************
Can you try instr(vbak.vbeln, SUBSTRING ('50',1,2)) as test and share the result ?
**/bic/azbsdoit100 - this is my BW table, just used an alias vbak for this.
Cheers
Anindya
After checking the documentation for
I don't find INSTR() to be a supported SQL function.
In fact the SAP HANA expression editor for the information views seems to be the only place where this function is actually officially defined.
Where do you find this to be a supported function?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Lars,
goning via http://help.sap.com/abapdocu_750/en/index.htm --> ABAP Keyword Documentation -> ABAP Dictionary -> ABAP CDS in ABAP Dictionary -> ABAP CDS Entities -> ABAP CDS DDL Statements -> ABAP CDS DEFINE VIEW -> ABAP CDS SELECT -> ABAP CDS SELECT Predefined Functions -> ABAP CDS sql_functions -> ABAP CDS String Functions
there, this function is listed.
Best,
Johannes
User | Count |
---|---|
90 | |
10 | |
10 | |
10 | |
7 | |
7 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.