dynamic sql statement

[Former Member]

hi experts

i have a doubt on dynamic sql statement

my dynamic table is p_table

dynamic internal table is <dyn_table>

dynamic work area is <dyn_table>

dynamic field is <dyn_field>

dynamic where condition is str_where.

Actually my sql select statement is

<b><u>1st query</u></b>

select stblg belnr gjahr bldat bukrs xblnr from bkpf

into corresponding fields of it_bkpf

for all entries in it_regup

where bukrs eq it_regup-zbukr

and belnr eq it_regup-belnr.

as i am going to use everything as dynamic how shall i write this above statement dynamically.

i made it like

select * from (p_table) into <dyn_table> where (str_where).

but i am not able to add line <b>for all entries in it_regup</b>. how to do the equlivent dynamic sql statement.

<b><u>2nd query</u></b>

my sql read statement is

read table it_bkpf into wa_bkpf

with key bukrs = wa_regup-zbukr

belnr = wa_regup-belnr

gjahr = wa_regup-gjahr.

how can i write the above query dynamically

i made it like

read table<dyn_table> into <dyn_wa>

with key (str_where).

but it is showing error. with key is not expected

<b><u>3rd query</u></b>

dynamic loop atatement

loop at<dyn_table> into <dyn_wa>.

i what to use where condition in loop then what would be my dynamic loop statement.

it is urgent, so request you to give solutions as soon as possible

if any body know all dynamic sql statement then give replay along with this query

[Former Member]

Hi ajit

For the second query, use

read table<dyn_table> into <b> table</b> <dyn_wa>

with key (str_where).

regards,

prasanth

• award if helpful

[Former Member]

hi prasanth

i used the table addition as per your suggestion. but i got the error as dyn_wa is not expected.

As <dyn_wa> is a dynamic work area not a table .

[seshatalpasai_madala]

Hi,

1) FOR ALL ENTRIES cannot be used dynamically as it is not partof FROM clause, not part of INTO clause and not part of WHERE clause.

2) Then instead of dyn_wa use dyn_table.

Since you are using INTO TABLE addition.

Regards,

Sesh

[Former Member]

hi sesh

i have used <dyn_table> in my 1st query not <dyn_wa>.

[seshatalpasai_madala]

Hi,

My second answer was for the Second wuery about READ.

Regards,

Sesh

[former_member784222]

Hi,

May be this answers your first question:

DATA: tabname LIKE dd03l-tabname VALUE 'MSEG'.

DATA: BEGIN OF it_mkpf OCCURS 0,
       mblnr LIKE mkpf-mblnr,
       mjahr LIKE mkpf-mjahr,
      END OF it_mkpf.

DATA: BEGIN OF field_list OCCURS 0,
        field(200),
      END OF field_list.

DATA: BEGIN OF query_tab OCCURS 0,
        query(200),
      END OF query_tab.


* building field list.

field_list-field = 'MBLNR'.
APPEND field_list.

field_list-field = 'MJAHR'.
APPEND field_list.

field_list-field = 'MATNR'.
APPEND field_list.

field_list-field = 'MENGE'.
APPEND field_list.



DATA dref TYPE REF TO data.

FIELD-SYMBOLS: <fs> TYPE ANY.
FIELD-SYMBOLS: <fs1> TYPE ANY.
FIELD-SYMBOLS: <fs2> TYPE STANDARD TABLE.


* creates dynamic data
CREATE DATA dref TYPE (tabname).
ASSIGN dref->* TO <fs>.


* query table
CONCATENATE 'MBLNR = ' '<fs2>-mblnr'   'AND' INTO query_tab-query SEPARATED BY space.
APPEND query_tab.
CLEAR: query_tab.

CONCATENATE 'MJAHR = ' '<fs2>-mjahr' '.' INTO query_tab-query SEPARATED BY space.
APPEND query_tab.
CLEAR: query_tab.




SELECT * INTO CORRESPONDING FIELDS OF TABLE it_mkpf FROM mkpf
              WHERE budat >= '20070101' AND budat <= '20070131'.


ASSIGN it_mkpf[] TO <fs2>.


SELECT (field_list) INTO CORRESPONDING FIELDS OF <fs> FROM (tabname) FOR ALL ENTRIES IN <fs2>  WHERE (query_tab).


  DO.
    ASSIGN COMPONENT  sy-index OF  STRUCTURE <fs> TO <fs1>.
    IF sy-subrc = 0.
      WRITE: <fs1>.
    ELSE.
      EXIT.
    ENDIF.
  ENDDO.

ENDSELECT.

Just try to adjust the code to suit your requirement.

Thanks and regards,

S. Chandra Mouli.

[Former Member]

this may helpful.

thaks

ajit