topic Substring in join or where clause in Application Development and Automation Discussions

Substring in join or where clause

Former Member — Thu, 28 Jun 2012 16:43:12 GMT

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.

Re: Substring in join or where clause

Former Member — Thu, 28 Jun 2012 17:32:46 GMT

Hi,

I don't think this is possible. Moreover I don't think you will gain something in term of performance... well, unless you have billion of Z reports

You code looks pretty fine to me. You have already move a select out of a loop, maybe you could also leave that MODIFY and use a field symbol instead...

loop at ti_tadir assigning <wa_tadir>.

<wa_tadir>-obj_name = <wa_tadir>-obj_name+1.

endloop.

Otherwise I think I would leave the code as is... Do you face any issue with it?

Cheers,

Manu.

Re: Substring in join or where clause

Former Member — Thu, 28 Jun 2012 18:28:10 GMT

Thank you for the field-symbol tip!

I'm not facing any issue. It's because the code would be cleaner.

Re: Substring in join or where clause

Former Member — Thu, 28 Jun 2012 18:39:55 GMT

Well... clean does not obligatory mean a minimum of lines

The way you are trying to join a table to itself is a good try but does not look so clean to me

This beeing said, I think this is possible with other languages by using some buit-in functions (like subtring( ) in abap)... Maybe in a future release?

Manu.

Re: Substring in join or where clause

Former Member — Fri, 29 Jun 2012 03:55:18 GMT

Hi,

I agree with Manu!

clean does not obligatory mean a minimum of lines

Suggestion: Since you used FOR ALL ENTRIES, it is better to check if itab is not empty. Check this link : Learn For All Entries - Things to consider before use.

If you insist to make your codes in minimum lines, I made some other way of coding. Feel free to try. Please observe the modification made.

 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.
 
 FIELD-SYMBOLS: <fs_tadir> TYPE ty_tadir,
                <fs_tadir_ok> TYPE ty_tadir.
 
 START-OF-SELECTION.
 
   SELECT obj_name
     INTO TABLE ti_tadir
     FROM tadir
    WHERE pgmid = 'R3TR' AND
          object = 'PROG'.
IF sy-subrc EQ 0.
 
     ti_tadir_ok = ti_tadir. " pass the data to second table
     DELETE ti_tadir WHERE obj_name NP 'Z*'. " delete data whose obj_name does not 'Z%' 

     LOOP AT ti_tadir ASSIGNING <fs_tadir>.
       READ TABLE ti_tadir_ok ASSIGNING <fs_tadir_ok> WITH KEY obj_name = <fs_tadir>-obj_name+1.
       IF sy-subrc EQ 0.
            WRITE:/ <fs_tadir>-obj_name.
       ENDIF.
     ENDLOOP.
 
   ENDIF.

Regards,

Jake

Re: Substring in join or where clause

Former Member — Mon, 03 Oct 2016 07:04:09 GMT

Hello,

I got it with new open SQL Syntax avalaible from enhancement 740.

For example:

Table ZHR_EXAMPLE_TABLE2 has the column ID_EMPLEADO with 6 characters instead of 8 like PERNR in table ZHR_EXAMPLE_TABLE1.

SELECT

T0~COLUMN1, T0~COLUMN2, T0~COLUMN3, T0~COLUM4, T1~COLUMN1 AS COLUMRESULT, T1~PERNR

FROM ZHR_EXAMPLE_TABLE1 AS T0

INNER JOIN ZHR_EXAMPLE_TABLE2 AS T1 ON SUBSTRING( T1~PERNR,3,6 ) = T0~ID_EMPLEADO

WHERE T0~IDPARTE = @ZIDPARTE AND T1~BEGDA LE T0~FECHA AND T1~ENDDA GE T0~FECHA

INTO CORRESPONDING FIELDS OF TABLE @ITAB.

IF SY-SUBRC EQ 0.

ENDIF.

Instead of "INTO CORRESPONDING FIELDS OF TABLE @ITAB" you can use "INTO TABLE @DATA(result)". With result no declarated before, the result of the select will be in the that table.

I hope it can be useful for someone.

Kind regards,

Julian.