How to implement the concat functionality in open sql

[Former Member]

eg,

I want to get sys_id,sys_client combined into a string from a table,

how can I write the open sql just like the functionality of 'concat(sys_id,sys_client)' in mysql sql,thanks.

[Former Member]

Hi

Check if the following helps...

CONCATENATE

Syntax

CONCATENATE {dobj1 dobj2 ...}|{LINES OF itab}

INTO result

[IN {BYTE|CHARACTER} MODE]

[SEPARATED BY sep]

[RESPECTING BLANKS].

Extras:

1. ... IN {BYTE|CHARACTER} MODE

2. ... SEPARATED BY sep

3. ... RESPECTING BLANKS

Effect

Either the content of the data objects dobj1, dobj2, ... or the rows in the internal tableitab are concatenated according to their order and assigned to the target field result.

If the target field result has a fixed length, and this is longer than the required length, it is filled on the right with spaces or hexadecimal 0. If the length of the target field is shorter, the concatenation is cut off on the right. If the target field is a string, its length is adjusted accordingly.

During string processing for data objects dobj1 dobj2 ... or rows in the internal table itab of fixed length, closing spaces are not normally taken into account.

System fieldsr

sy-subrc Meaning

0 The contents of all data objects dobj1 dobj2 ... or itab rows have been transferred to the target field result.

4 The contents of the data objects dobj1 dobj2 ... or itab rows could not be transferred completely, as result is too short.

Addition 1

... IN {BYTE|CHARACTER} MODE

Effect

The optional addition IN {BYTE|CHARACTER} MODE determines whether byte or string processing is performed. If the addition is not specified, string processing is performed. Depending on the type of processing, the data objects dobj1 dobj2 ... or the row type in the internal table itab and the separator sep must be byte or character type.

Addition 2

... SEPARATED BY sep

Effect

With the addition SEPARATED BY , the content of data object sep is inserted between the content of the subsequent data objects dobj1 dobj2 .... During string processing with separators sep of fixed length, the closing space is taken into account.

Example

After the first CONCATENATE statement, the result contains "Wehaveallthetimeintheworld", while after the second it contains "We have all the time in world".

DATA: t1 TYPE c LENGTH 10 VALUE 'We',

t2 TYPE c LENGTH 10 VALUE 'have',

t3 TYPE c LENGTH 10 VALUE 'all',

t4 TYPE c LENGTH 10 VALUE 'the',

t5 TYPE c LENGTH 10 VALUE 'time',

t6 TYPE c LENGTH 10 VALUE 'in',

t7 TYPE c LENGTH 10 VALUE 'the',

t8 TYPE c LENGTH 10 VALUE 'world',

result TYPE string.

CONCATENATE t1 t2 t3 t4 t5 t6 t7 t8

INTO result.

...

CONCATENATE t1 t2 t3 t4 t5 t6 t7 t8

INTO result SEPARATED BY space.

Addition 3

... RESPECTING BLANKS

Effect

The addition RESPECTING BLANKS is only allowed during string processing and causes the closing spaces for data objects dobj1 dobj2 ... or rows in the internal table itab to be taken into account. Without the addon, this is only the case with string.

Note

With addition RESPECTING BLANKS, statement CONCATENATE can be used in order to assign any character strings EX>text - taking into account the closing empty character - to target str of type string: CLEAR str. CONCATENATE str text INTO str RESPECTING BLANKS.

Example

After the first CONCATENATE statement, result contains "When_the_music_is_over", after the second statement it contains "When______the_______music_____is________ over______" . The underscores here represent blank characters.

TYPES text TYPE c LENGTH 10.

DATA itab TYPE TABLE OF text.

DATA result TYPE string.

APPEND 'When' TO itab.

APPEND 'the' TO itab.

APPEND 'music' TO itab.

APPEND 'is' TO itab.

APPEND 'over' TO itab.

CONCATENATE LINES OF itab INTO result SEPARATED BY space.

...

CONCATENATE LINES OF itab INTO result RESPECTING BLANKS.

regards

dinesh

[Former Member]

Hi Ray,

I don't think there is a direct CONCAT option.

Instead you can get the result in two different variables and concatenate them.

select single sys_id, sys_client into (id, client) from <table>
concatenate sys_id sys_client into final_string.

Regards

Anil Madhavan