Skip to Content
0
Former Member
Jun 28, 2012 at 04:43 PM

Substring in join or where clause

4391 Views

Hi,

I would like a code like this:

select distinct t1~obj_name
into table ti_tadir
from tadir as t1 join tadir as t2 on t1~obj_name+1 = t2~obj_name
where t1~pgmid = 'R3TR' and
t1~object = 'PROG' and
t1~obj_name like 'Z%'.

Error: Field t1~obj_name+1 is unknown.

Is there a function that get the substring of column? Do I have to use a native sql?

If I could specify this condition in the query, much of the code below would not be needed.

REPORT YYLE_COPIAS.

types: begin of ty_tadir,
obj_name type tadir-obj_name,
end of ty_tadir.

data: ti_tadir type standard table of ty_tadir,
ti_tadir_ok type standard table of ty_tadir,
wa_tadir like line of ti_tadir.

start-of-selection.

select obj_name
into table ti_tadir
from tadir
where pgmid = 'R3TR' and
object = 'PROG' and
obj_name like 'Z%'.

loop at ti_tadir into wa_tadir.
wa_tadir-obj_name = wa_tadir-obj_name+1.
modify ti_tadir from wa_tadir.
endloop.

select obj_name
into table ti_tadir_ok
from tadir
for all entries in ti_tadir
where pgmid = 'R3TR' and
object = 'PROG' and
obj_name = ti_tadir-obj_name.

loop at ti_tadir_ok into wa_tadir.
concatenate 'Z' wa_tadir-obj_name into wa_tadir-obj_name.
write: / wa_tadir-obj_name.
endloop.

Regards,

Leandro.