topic dynamic select statement in Application Development and Automation Discussions

dynamic select statement

Former Member — Wed, 18 Feb 2009 09:14:37 GMT

Hi all,

I am going to use the SELECT statement with INNER JOIN. When I use the table names(at 2 places), it is working properly. When I use the dynamic names, it is giving syntax errors.

Can you please explain me how to use the dynamic names for SELECT statement with INNER JOINs..

Ex:

SELECT a_{fieldname1 b}fieldname2

INTO CORRESPONDING FIELDS OF TABLE lt_table

FROM (lv_tabname) AS a INNER JOIN

(lv_ttabname) AS b ON ( a_{fieldname1 = b}fieldname1 AND

b~fieldname2 = sy-langu ).

(This is not working.......)

Thanks and Regards,

Balakrishna.N

Re: dynamic select statement

Former Member — Wed, 18 Feb 2009 09:16:16 GMT

Did you try by removing brackets?

Re: dynamic select statement

Former Member — Wed, 18 Feb 2009 09:19:10 GMT

Hi check the sample code below.

DATA: I_TAB TYPE REF TO DATA,
        NEW_LINE TYPE REF TO DATA.
  FIELD-SYMBOLS: <TAB> TYPE TABLE,
                 <DYN_WA>.
  CREATE DATA I_TAB TYPE TABLE OF (P_TABLE).
  ASSIGN I_TAB->* TO <TAB>.

*Create dynamic work area and assign to FS
  CREATE DATA NEW_LINE LIKE LINE OF <TAB> .
  ASSIGN NEW_LINE->* TO <DYN_WA>.

  SELECT *
         FROM (P_TABLE)
         INTO TABLE <TAB>.

It worked for me.

Regards,

Murthy.

Re: dynamic select statement

Former Member — Wed, 18 Feb 2009 09:20:31 GMT

Thank you very much for your quick reply.

Then also it will syntax error as the variable lv_tabname is not defined in data dictionary. lv_tabname is a local variable.

Regards,

Balakrishna.N

Re: dynamic select statement

Former Member — Wed, 18 Feb 2009 09:22:05 GMT

Concatenate lv_tabname 'as a inner join' lv_ttabname 'as b on ' into $temp separated by space.

concatenate $temp 'a_{fieldname1 = b}fieldname1 and b~fieldname2 = sy-langu ' into $temp

select a_{fieldname1 b}fieldname2

INTO CORRESPONDING FIELDS OF TABLE lt_table

FROM ($temp)

Re: dynamic select statement

Former Member — Wed, 18 Feb 2009 09:23:06 GMT

Hi,

Try below

data: lv_tabname type RSRD1-TBMA_VAL.   "Variable  type should be like this.
data: lv_ttabname type RSRD1-TBMA_VAL.


SELECT a~fieldname1 b~fieldname2
INTO CORRESPONDING FIELDS OF TABLE lt_table
FROM lv_tabname  AS a INNER JOIN
 lv_ttabname  AS b ON ( a~fieldname1 = b~fieldname1 ) AND
( b~fieldname2 = sy-langu ).

hope it works.

Regards

Re: dynamic select statement

Former Member — Wed, 18 Feb 2009 09:23:25 GMT

It works fine if we are not going to use the inner or outer joins....... Even without field symbols we can use the dynamic table name. Here we need to use the INTO CORRESPONDING FIELDS OF .....

Thank you very much for you quick reply Murthy.

Thanks and Regards,

Balakrishna.N

Re: dynamic select statement

Former Member — Wed, 18 Feb 2009 09:24:55 GMT

It will give the syntax error by saying lv_tabname is not defined in data dictionary as it is local variable....

Thanks and Regards,

Balakrishna.N

Re: dynamic select statement

Former Member — Wed, 18 Feb 2009 09:38:25 GMT

Hi,

Find the sample code below.

DATA: I_TAB TYPE REF TO DATA,
        NEW_LINE TYPE REF TO DATA.
  FIELD-SYMBOLS: <TAB> TYPE TABLE,
                 <DYN_WA>.
  CREATE DATA I_TAB TYPE TABLE OF (P_TABLE).
  ASSIGN I_TAB->* TO <TAB>.

*Create dynamic work area and assign to FS
  CREATE DATA NEW_LINE LIKE LINE OF <TAB> .
  ASSIGN NEW_LINE->* TO <DYN_WA>.

  SELECT *
         FROM (P_TABLE)
         INTO TABLE <TAB>.

It worked for me, in your case I believe passing the fields of the intended dynamically passed data base table is posing problems.

You cannot pass the where conditions. Instead individually select data into two seperate internal tables. Then generate the combined internal table.

Hope this helps.

Murthy

Re: dynamic select statement

faisalatsap — Wed, 18 Feb 2009 09:51:14 GMT

Hi,

Test the following Sample Code hope will help you out.

KDATA: it_kan1 LIKE STANDARD TABLE OF kna1 WITH HEADER LINE,
      name_of_table(10).

name_of_table = 'kna1'.
SELECT * FROM (name_of_table) UP TO 10 ROWS
  INTO CORRESPONDING FIELDS OF TABLE it_kan1.

Kind Regards,

Faisal

Re: dynamic select statement

Former Member — Wed, 18 Feb 2009 11:20:06 GMT

This is giving dump as it will takes $temp as table name as it comes immediately after FROM.....

Thanks and Regards,

Balakrishna.N

Re: dynamic select statement

faisalatsap — Wed, 18 Feb 2009 11:46:01 GMT

Hi, Balakrishna

Test the following Code i have tested and it is working FINE :).

DATA: it_vbak LIKE STANDARD TABLE OF vbak WITH HEADER LINE.
BREAK-POINT.
Data: $temp TYPE string,
      lv_tabname type string,
      lv_ttabname type string.
lv_tabname = 'vbap'.
lv_ttabname = 'vbak'.
Concatenate lv_tabname 'inner join ' lv_ttabname ' on (vbap~vbeln = vbak~vbeln)' into $temp separated by space.

select vbak~vbeln
INTO CORRESPONDING FIELDS OF TABLE it_vbak
FROM ($temp).

Please Reply if any Issue,

Kind Regards,

Faisal

Re: dynamic select statement

faisalatsap — Wed, 18 Feb 2009 13:13:05 GMT

Hi, Balakrishna

Test the following Code too if you want the use WHERE Condition too.

DATA: it_vbak LIKE STANDARD TABLE OF vbak WITH HEADER LINE.

DATA: $fields TYPE string,
      $dbtable TYPE string,
      $where TYPE string,
      $it TYPE string,
      lv_tabname TYPE string,
      lv_ttabname TYPE string.
lv_tabname = 'vbap'.
lv_ttabname = 'vbak'.
CONCATENATE: 'vbak~vbeln' 'vbak~erdat' INTO $fields SEPARATED BY space,
             lv_tabname 'inner join ' lv_ttabname ' on ( vbap~vbeln = vbak~vbeln )' INTO $dbtable SEPARATED BY space,
             'vbap~vbeln eq ''0000000001''' 'or' 'vbap~vbeln eq ''0000000002''' INTO  $where SEPARATED BY space.
SELECT ($fields)
  INTO CORRESPONDING FIELDS OF TABLE it_vbak
  FROM ($dbtable)
  WHERE ($where).

Kind Regards,

Faisal