topic Re: dynamic sql statement in Application Development and Automation Discussions

dynamic sql statement

Former Member — Tue, 03 Jul 2007 11:09:32 GMT

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

1st query

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 for all entries in it_regup. how to do the equlivent dynamic sql statement.

2nd query

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

3rd query

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

Re: dynamic sql statement

Former Member — Tue, 03 Jul 2007 11:13:42 GMT

Hi ajit

For the second query, use

read table<dyn_table> into table <dyn_wa>

with key (str_where).

regards,

prasanth

award if helpful

Re: dynamic sql statement

Former Member — Tue, 03 Jul 2007 12:39:35 GMT

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 .

Re: dynamic sql statement

seshatalpasai_madala — Tue, 03 Jul 2007 12:42:23 GMT

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

Re: dynamic sql statement

Former Member — Tue, 03 Jul 2007 12:56:27 GMT

hi sesh

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

Re: dynamic sql statement

seshatalpasai_madala — Tue, 03 Jul 2007 12:59:13 GMT

Hi,

My second answer was for the Second wuery about READ.

Regards,

Sesh

Re: dynamic sql statement

former_member784222 — Fri, 13 Jul 2007 03:34:29 GMT

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.

Re: dynamic sql statement

Former Member — Tue, 07 Oct 2008 14:01:01 GMT

this may helpful.

thaks

ajit