topic Dynamic internal table and dynamic select in Application Development and Automation Discussions

Dynamic internal table and dynamic select

Former Member — Sat, 07 Jan 2006 04:42:23 GMT

Hi,

I have doubt regarding making synamic seect and dynamic internal table.

Please help me out.

Consider following:

T_UNIQUE Table (only one field FNAME)

FNAME

STCD1

STCD2

then,

Data: begin of t_lfa1,

LIFNR LIKE LFA1-LIFNR,

STCD1 LIKE LFA1-STCD1,

STCD2 LIKE LFA1-STCD2,

end of t_lfa1.

Select LFA1_{LIFNR LFA1}STCD1 LFA1_{STCD2 FROM LFA1 INTO TABLE t_lfa1 WHERE LFA1}LAND1 = 'BR'

Now,

T_Unique Table (only one field FNAME)

FNAME

STCD1

STCD2

STCD3

STCD4

then,

Data: begin of t_lfa1,

LIFNR LIKE LFA1-LIFNR,

STCD1 LIKE LFA1-STCD1,

STCD2 LIKE LFA1-STCD2,

STCD3 LIKE LFA1-STCD3,

STCD4 LIKE LFA1-STCD4,

end of t_lfa1.

Select LFA1_{LIFNR LFA1}STCD1 LFA1_{STCD2 LFA1}STCD3 LFA1_{STCD4 into table t_lfa1 WHERE LFA1}LAND1 = 'BR'.

Hence depending on number of records in FNAME field in table T_UNIQUE the select statement should be built dynamically.

How do I do make this selection dynamic in SQL?

Note: From above you can see that we also need the internal table to be dynamic.

How do I make the internal table t_lfa1 also dynamic ?

Re: Dynamic internal table and dynamic select

Former Member — Sat, 07 Jan 2006 04:54:23 GMT

Hi Tushar,

1. Select LFA1_{LIFNR LFA1}STCD1 LFA1_{STCD2 LFA1}STCD3 LFA1_{STCD4 into table t_lfa1 WHERE LFA1}LAND1 = 'BR'.

One way is instead of constructing dynamic

select (for list of fields)

we can just use

select * from table

INTO CORRESPONDING FIELDS OF TABLE t_lfa1

where Condition.

Regards,

Amit M.

Re: Dynamic internal table and dynamic select

Former Member — Sat, 07 Jan 2006 04:56:36 GMT

Is it guaranteed that all the fields are from LFA1 only? If not I think, you also need the table name in your T_UNIQUE table.

Re: Dynamic internal table and dynamic select

Former Member — Sat, 07 Jan 2006 05:00:50 GMT

Yes it is guarated that all the fields are from LFA1 only.

Re: Dynamic internal table and dynamic select

Former Member — Sat, 07 Jan 2006 05:02:52 GMT

All the fields are from LFA1 only and also I can't use selct * because that is not allowed here in my company due to performance issue.

Please tell me how do I do dynamic slect exact for the situation I mentioned and also how do i make dynamci internal table ?

Do I need to use some class and objects ans stuff ? If so how ?

Re: Dynamic internal table and dynamic select

Former Member — Sat, 07 Jan 2006 05:12:25 GMT

This is a very very crucial requirement without which its very hard to move further. Please help me out ABAP gurus.

Thanks.

Re: Dynamic internal table and dynamic select

Former Member — Sat, 07 Jan 2006 05:14:31 GMT

Hi again,

1. It is clear that we have

to build dynamic sql from

the fields of the internal table.

2. take this code (just copy paste)

and u will understand the logic.

3. In the final, in debug, see internal table ITAB.

The sql is constructed dynamic

and the fields of internal table are got from FM

GET_COMPONENT_LIST

It works fantastic.

REPORT abc.

*----

DATA : BEGIN OF itab OCCURS 0,

lifnr LIKE lfa1-lifnr,

land1 LIKE lfa1-land1,

name1 LIKE lfa1-name1,

name2 LIKE lfa1-name2,

END OF itab.

DATA : allfields(300) TYPE c.

DATA : FLD(100) TYPE c.

DATA : components LIKE rstrucinfo OCCURS 0 WITH HEADER LINE.

*----

CALL FUNCTION 'GET_COMPONENT_LIST'

EXPORTING

program = sy-repid

fieldname = 'ITAB'

TABLES

components = components.

BREAK-POINT.

LOOP AT components.

CONCATENATE 'LFA1~' components-compname '' INTO FLD.

CONCATENATE ALLFIELDS FLD INTO ALLFIELDS SEPARATED BY SPACE.

ENDLOOP.

*----

SELECT (allfields)

FROM lfa1

INTO TABLE itab.

*----

BREAK-POINT.

regards,

amit m.

Re: Dynamic internal table and dynamic select

Former Member — Sat, 07 Jan 2006 05:19:53 GMT

It seems clear as how to do dynamci SQL selection but again how do I build dynamicinternal table ?

Thanks.

Re: Dynamic internal table and dynamic select

Former Member — Sat, 07 Jan 2006 05:55:07 GMT

You may need something like a dynamic subroutine. Here is an example.


REPORT ztestaks.

TYPES: t_source(72).
 
DATA: v_program(8),
      v_err_message(128),
      v_error_line TYPE i.
 
DATA: i_subroutine_code TYPE table of t_source,
      s_source          TYPE t_source.
 
s_source = 'REPORT ZDYNAMICPROG'.
APPEND s_source TO i_subroutine_code.
 
*-- Now prepare the code for the dynamic subroutine
s_source = 'FORM DYNAMIC_SELECT.'.
APPEND s_source TO i_subroutine_code .

*-- Prepare the code for declaring the internal table
s_source = 'DATA: BEGIN OF T_LFA1,'.
APPEND s_source TO i_subroutine_code.

*-- this will dynamically build the fields of internal table
LOOP AT t_unique.
  CLEAR s_source.
  CONCATENATE t_unique-fname
              'LIKE'
              'LFA1-'
         INTO s_source SEPARATED BY SPACE.
  CONCATENATE s_source
              t_unique-fname
              ','
         INTO s_source.
  APPEND s_source TO i_subroutine_code.
ENDLOOP.

s_source = 'END OF T_LFA1.'.
APPEND s_source TO i_subroutine_code.

*-- Now prepare the code for the select statement
s_source = 'SELECT'.
APPEND s_source TO i_subroutine_code.
LOOP AT t_unique.
  s_source = t_unique-fname.
  APPEND s_source TO i_subroutine_code.
ENDLOOP.

s_source = 'FROM LFA1'
APPEND s_source TO i_subroutine_code.

s_source = 'INTO TABLE T_LFA1'.
APPEND s_source TO i_subroutine_code.

s_source = 'WHERE LAND1 = 'BR'.
APPEND s_source TO i_subroutine_code.

s_source = 'ENDFORM.'.
APPEND s_source TO i_subroutine_code.
 
generate subroutine pool i_subroutine_code 
                    name v_program
                 message v_err_message
                    line v_error_line.
if sy-subrc = 0.
  perform DYNAMIC_SELECT in program (v_program).
else.
  write:/ v_err_message.
endif.

But the problem with this is that your internal table t_lfa1 is no longer globally visible in your program. It will only be local to the dynamic subroutine.

I wrote this on notepad, so you may need to tweak it a little bit to make it correct.

See if you can somehow use this and combine the dynamic internal table creation using OO(search the forum for dynamic internal table).

Srinivas

Re: Dynamic internal table and dynamic select

Former Member — Sat, 07 Jan 2006 06:45:02 GMT

Hi Tushar,

1. See my code.

2. Important Points are :

3. I have created one FORM which will

CONSTRUCT dynamic internal table,

but for this we have to

declare some COMPULSORY variables (as mentioned in my code)

Also we have to provide the FORM,

the LIST OF FIELDNAMES.

(as mentioned in my code)

4. The name of the dynamic internal table will be

5. Using this, we can use SELECT (see my code)

6. Printing/using FIELDS from this dynamic table,

is a little tricky,

(I have mentioned the same in my code)

7. My Code (Just copy paste in new program

and it will run fantastic)

REPORT abc.

*----

COMPULSORY

FIELD-SYMBOLS: <dyntable> TYPE ANY TABLE.

FIELD-SYMBOLS: <dynline> TYPE ANY.

DATA: lt TYPE lvc_t_fcat.

DATA: ls TYPE lvc_s_fcat.

FIELD-SYMBOLS: <fld> TYPE ANY.

DATA : fldname(50) TYPE c.

*----

FIELD LIST

ls-fieldname = 'LIFNR'.

APPEND ls TO lt.

ls-fieldname = 'LAND1'.

APPEND ls TO lt.

ls-fieldname = 'NAME1'.

APPEND ls TO lt.

*----

PERFORM

PERFORM mydyntable USING lt.

*----

SELECT

SELECT * FROM lfa1 INTO CORRESPONDING FIELDS OF TABLE <dyntable>.

*----

DISPLAY

LOOP AT <dyntable> ASSIGNING <dynline>.

fldname = '<DYNLINE>-LIFNR'.

ASSIGN (fldname) TO <fld>.

WRITE 😕 <FLD>.

fldname = '<DYNLINE>-LAND1'.

ASSIGN (fldname) TO <fld>.

WRITE : <FLD>.

fldname = '<DYNLINE>-NAME1'.

ASSIGN (fldname) TO <fld>.

WRITE : <FLD>.

endloop.

break-point.

*----

FORM

*----

form mydyntable using lt type lvc_t_fcat .

*----

Create Dyn Table From FC

field-symbols: <fs_data> type ref to data.

field-symbols: <fs_1>.

field-symbols: <fs_2> type any table.

data: lt_data type ref to data.

assign lt_data to <fs_data>.

call method cl_alv_table_create=>create_dynamic_table

exporting

it_fieldcatalog = lt

importing

ep_table = <fs_data>

exceptions

generate_subpool_dir_full = 1

others = 2.

if sy-subrc <> 0.

endif.

*----

Assign Dyn Table To Field Sumbol

assign <fs_data>->* to <fs_1>.

assign <fs_1> to <fs_2>.

assign <fs_1> to <dyntable>.

endform. "MYDYNTABLE

regards,

amit m.

Re: Dynamic internal table and dynamic select

former_member186741 — Mon, 09 Jan 2006 01:08:22 GMT

have a look at Rich Heilman's blog on this subject:

/people/rich.heilman2/blog/2005/07/27/dynamic-internal-tables-and-structures--abap