select with last two char

[former_member598551]

Hi all,

I want to write a cod for a new field of generic datasource in cmod. I should use select and one of conditions is last two characters of usnam field. I have to select values from table, with condition of USNAM field's last two characters. IF last two characters of USNAM field equel to 'MT' , 'TN' or 'SH', i will bring them in table which used in Loop. Length of USNAM char is 12 and some example for usnam values: 1234MT, 123MT etc. How can i write this select? Could you please help me about the issue?

Thanks.

[Sandra_Rossi · Accepted solution]

Use LIKE '%MT' etc. Don't use LIKE 'MT%', you can understand the difference.

Of course, that means the whole table will have to be scanned so maybe if the selection is only something less than 5% (arbitrary number from me) of rows and your query is frequently used, it could be more efficient to query with an index by user name, you'd better search first the list of existing users in users table USR01, and join with your table:

SELECT crm_jcds~*
FROM crm_jcds
INNER JOIN usr01
ON crm_jcds~usnam = usr01~bname
INTO CORRESPONDING FIELDS OF TABLE lt_jcds6
FOR ALL ENTRIES IN is_source50
WHERE objnr = is_source50-guid
AND inact EQ ' '
AND stat EQ 'E0004'
AND ( chind EQ 'I' OR chind EQ 'U' )
AND ( usr01~bname LIKE '%MT' OR ...

But if your query is rarely used, then don't create an index, don't join with USR01, it will be just slow, but it's rarely used. Ask your database administrator to help you if you need more information.

[sgassem]

You can use STRLEN function like below.

len = strlen ( variable ) - 2.

last_two = variable+len(2).

if last_two = 'MT'.

then do what you need to do.

endif.

[former_member598551]

Hi Sam,

Thank you for your help and answer. I couldn't be sure to tell issue correctly. I wanna change condition about usnam field as last two characters of usnam field. I have the problem here.

SELECT *
FROM crm_jcds INTO CORRESPONDING FIELDS OF TABLE lt_jcds6
FOR ALL ENTRIES IN is_source50
WHERE objnr = is_source50-guid
AND inact EQ ' '
AND stat EQ 'E0004'
AND ( chind EQ 'I' OR chind EQ 'U' )
AND ( usnam Like 'MT%' OR usnam LIKE 'SH%' OR usnam LIKE 'TN%' ).

Thanks.