topic Re: Concat function or equivalent for CDS view? in Application Development and Automation Discussions

Concat function or equivalent for CDS view?

Former Member — Thu, 19 Jun 2014 14:56:28 GMT

Trying to create a CDS view and looking to combine multiple character fields (e.g. first_name + last_name) and am wondering if this is possible? Am on CRM NW 7.4 SP5 on HANA.

Using the arithmetic function gives me the following.

Data type CHAR is currently not supported in an arithmetic expression

Looking at the official documentation here, it doesn't appear to be included?

ABAP Keyword Documentation

I can always create a AMDP or even a HANA native view, just wondering if anyone has come across the same issue and found a solution within CDS view?

Thanks in advance,
Sean.

Re: Concat function or equivalent for CDS view?

jasmin_gruschke — Fri, 20 Jun 2014 07:11:25 GMT

Hi Sean,

there's no concatenate function as of 7.4 SP5.

A workaround in OpenSQL (if that's the combination you use), you could use the columns of your choice in the projection list like (I use literals in the example for simplicity):

{

'a' as a,

'b' as b

}

and concatenate later by means of OpenSQL:

select a && b from <cds_view> into ….

However, if you want to use the view in an ALV this does not help you, I know…

Cheers,

Jasmin

Re: Concat function or equivalent for CDS view?

Former Member — Fri, 20 Jun 2014 08:17:29 GMT

Thanks Jasmin, thanks for the confirmation and work around solution.

Much appreciated,

Sean.

Re: Concat function or equivalent for CDS view?

Former Member — Wed, 08 Oct 2014 17:48:25 GMT

Hi,

Currently we're at support pack 7 for AS ABAP. But seems that it's still not possible right?

Actually I created an CDS view and wanted to use it within a FOR ALL ENTRIES comparing a documentnr and document item nr. With using both of the fields the performance was really low. But when I remove the document item nr, it was 8 times faster. So I wanted to create one field with the document and item number concatenated, so that I only have 1 field to compare within the FOR ALL ENTRIES.

Thanks in advance.

Regards,

Bart

Re: Concat function or equivalent for CDS view?

jasmin_gruschke — Thu, 09 Oct 2014 05:32:31 GMT

Hi Bart,
the discussion was about concatenation of fields using Open SQL syntax, which is possible as of AS ABAP 7.4 SP05.
However, as I understand, you are asking about concatenation in a CDS view, right? This is possible, yet only as of Support Package 08.

Best,

Jasmin

Re: Concat function or equivalent for CDS view?

freek_cavens2 — Mon, 27 Oct 2014 16:12:06 GMT

Will it also be possible to use such syntax in joins (concatenations, substrings,...)? I would like to do something like this :

@AbapCatalog.sqlViewName: 'ZV_CDS_INVC'

define view zcdsv_rbkp_bkpf as

select from rbkp as a inner join bkpf as b

on CONCAT(a.belnr,a.gjahr) = b.awkey

{

key a.belnr,

key a.gjahr }

If I do this directly on the database, this is not a problem. In Open SQL I have to solve this with a for all entries and it would be much easier if I could use CDS for this (just finished the OpenSap course on Abap development on Hana and ready to go ).

Regards,

Freek

Re: Concat function or equivalent for CDS view?

thomasgauweiler — Tue, 28 Oct 2014 07:39:14 GMT

Hi Freek,

this functionality will surely come, but it will take some time until available.

Until then you can help yourself by creating two views: In the first view build a new column with the concatenation and then use this view and its column in a second view for the join condition.

Best Regards, Thomas

Re: Concat function or equivalent for CDS view?

freek_cavens2 — Tue, 28 Oct 2014 10:45:25 GMT

Hi Thomas,

Thank you for the information. The work-around is definitely useful.

Regards,

Freek

Re: Concat function or equivalent for CDS view?

klaus_herter — Thu, 08 Jan 2015 09:44:32 GMT

Hi Sean,

is your original request solved by the CONCAT function within the select list:

CONCAT(user.first_name, user.last_name) as ...

I was wondering the same way as you but this was the only thing that I found with a missing space ?

BEST, Klaus

Re: Concat function or equivalent for CDS view?

jasmin_gruschke — Thu, 08 Jan 2015 11:44:07 GMT

Hi Klaus,
yes, CONCAT is available as of SP8, see http://scn.sap.com/community/abap/blog/2014/10/10/abap-news-for-740-sp08--abap-core-data-services-cds.

Cheers,

Jasmin

Re: Concat function or equivalent for CDS view?

former_member186444 — Mon, 15 Jun 2015 08:25:52 GMT

Hi Jasmin,

we're on SP9 and I have a need to use CONCAT but I'm getting an error saying "this function or expression is not supported at this point". Any idea why that would be?

thanks,

Malcolm.

Re: Concat function or equivalent for CDS view?

jasmin_gruschke — Mon, 15 Jun 2015 08:54:10 GMT

Hi Malcolm,
you are trying to use it in a CDS View (DDL Source) in the ABAP?

Where exactly do you try to use it? In the projection list, the ON condition of a JOIN/ASSOCIATION, in the WHERE clause, ...?

What's the exact error message, can you please paste it here (maybe even including the TRMSG message number - you can find it in the problems view after adding in the problems view this dedicated column).

Cheers,
Jasmin

Re: Concat function or equivalent for CDS view?

former_member186444 — Mon, 15 Jun 2015 09:55:43 GMT

Hi Jasmin,

yes, CDS View in the ON condition of a JOIN.

and the error is "this function or expression ('CONCAT') is not supported at this point". I opened the problems view but can't see a message number.

thanks,

Malcolm.

Re: Concat function or equivalent for CDS view?

jasmin_gruschke — Mon, 15 Jun 2015 11:38:13 GMT

Hi Malcolm,

to see the message ID, you'd have to click in the problems view on the "drop down" icon. There you can select "Configure Columns..." and move "ID" from the "Hidden" to "Shown" columns.

But comming to your issue. The CONCAT function is not allowed or let's say supported at that position. You can use a view on view as a workaround, i.e. a intermediat view "z_intermed_view" having the CONCAT('S', a.mc_short ) as prefixed_with_s in the projection list and then do a left outer join to z_intermed_view with prefixed_with_s in the ON condition.

Best,
Jasmin

Re: Concat function or equivalent for CDS view?

former_member186444 — Tue, 16 Jun 2015 08:12:32 GMT

thanks Jasmin. That worked well.

Re: Concat function or equivalent for CDS view?

freek_cavens2 — Fri, 19 Feb 2016 14:49:10 GMT

Hi Thomas,

I have most recently come up with another issue with the concat function. I have created a view to solve the problem above (the concat to fix the RBKP-BKPF join) as per your work around. The problem that I'm now experiencing is that, while the concatenated field I created contains the key fields belnr and gjahr from the RBKP table, when I use this field in a query, no index is used on the database. So my next question is :

- How can I get the query/CDS view to use an index on the underlying table?

- Can I create an index on the new column itself on the CDS view?

My view is this (I only kept the relevant columns)

@AbapCatalog.sqlViewName: 'zrbkp_concat'

@ClientDependent: true

@AbapCatalog.compiler.CompareFilter: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'CDS view on RBKP with BELNR/GJAHR concatenation'

define view Zcds_Rbkp_Concat as select from rbkp {

key belnr,

key gjahr,

concat(belnr, gjahr) as awkey

}

Re: Concat function or equivalent for CDS view?

thomasgauweiler — Fri, 19 Feb 2016 15:44:00 GMT

Hi Freek,

you can neither build an index for a CDS view column nor can you force the database to reverse the concat function (when using the concatenated field in an ON condition or a WHERE condition) as the function is not bijektiv.

Rule of thumb: avoid expressions for key fields!

Best Regards, Thomas