Dynamic join table

[Former Member]

Hi expert. i wish to create a program that can join 2 dynamic table, some of the point i cant figure out the solution, so wish the expert here can help me.

Like the code below:

table_1 and table_2 is the parameters.

field2 is the primary key of table_1 and table_2

where_tab is the dynamic SQL condition.

DATA: pre_itab1 TYPE REF TO data.

FIELD-SYMBOLS: <fs_itab1> TYPE STANDARD TABLE.

DATA: pre_itab2 TYPE REF TO data.

FIELD-SYMBOLS: <fs_itab2> TYPE STANDARD TABLE.

CREATE DATA pre_itab1 TYPE STANDARD TABLE OF (table_1).

ASSIGN pre_itab1->* TO <fs_itab1>.

CREATE DATA pre_itab2 TYPE STANDARD TABLE OF (table_2).

ASSIGN pre_itab1->* TO <fs_itab2>.

ASSIGN pre_itab2->* TO <fs_itab2>.

IF table_1 NE '' AND table_2 NE ''.

SELECT *

FROM (table_1)

INNER JOIN (table_2) on (table_1)_{(field2) = (table_2)}(field2)

INTO table <fs_itab2>

WHERE (where_tab).

ELSEIF table_1 NE ''.

SELECT *

FROM (table_1)

INTO table <fs_itab1>

where (where_tab).

ENDIF.

there had 2 problem or more that i even didnt realise.

1. the inner join part

2. the table size that create on field-symbols for <fs_itab2>.

Please give advice for how to solve it.

i'll appreciate for ur reply.

[Former Member]

Hi

I hope this sample can help you:

DATA: POSNR TYPE VBAP-POSNR.
DATA: FROM_TABLE(100).
DATA: FIELD_TABLE(10) TYPE C OCCURS 0 WITH HEADER LINE.

FROM_TABLE = 'VBAK INNER JOIN VBAP ON VBAK~VBELN = VBAP~VBELN'.

FIELD_TABLE = 'POSNR'.
APPEND FIELD_TABLE.


SELECT (FIELD_TABLE) INTO POSNR FROM (FROM_TABLE)..
  WRITE / POSNR.
  if sy-dbcnt > 10. exit. endif.
ENDSELECT.

Max

Edited by: max bianchi on Jan 12, 2009 11:50 AM

[Former Member]

Thanks max for fast respond.

ur sample were help me to solve my inner join problem.

thanks.

but i still cant solve the 2nd problem which is the table size of the join table,

the problem i cant solve bcoz 2 of the input is dynamic tables, (like mara join marc)

means that i must have a dynamic table structure which consist of all the dynamic field

of mara and marc that allow me to store 2 of the dynamic input tables.

abit messy, hope u can understand what i need.

[mvoros]

Hi,

have you tried to use "INTO CORRESPONDING FIELDS OF TABLE table_name" instead of "INTO table_name". I am not sure but it may work.

[Former Member]

Hi

I believe it can't do it using INNER JOIN, u could several problem, for example if the 2 tables have fields having the same name.

I think it can be easier to use to different selections for the 2 table instead of the join.

Max

[Former Member]

To Martin,

that is one of the way that allow me to store the value into a table. but it not really the idea from me b4 this.

but it allow me to avoid syntax error. thanks anyway.

[Sandra_Rossi]

you must use RTTC runtime type creation (you may also search for RTTI as people sometimes use this term), to create dynamically your internal table. Search forum, wikis, blogs. There are lots of examples.

[Former Member]

To max,

what is the 'different selections' u mention?

now i realise that it's impossible have all the field of 2 dynamic table,

like example mara have 149 field and marc have 159 field,

so if i need to create a dynamc field that consist all of the field.

it's impossible man.

so now i have to change my idea. any idea recommend?

mayb can display the first 15 field

following is the temporary undone but workable code.

DATA: pre_itab1 TYPE REF TO data.

FIELD-SYMBOLS: <fs_itab1> TYPE STANDARD TABLE.

CREATE DATA pre_itab1 TYPE STANDARD TABLE OF (table_1).

ASSIGN pre_itab1->* TO <fs_itab1>.

DATA: from_table1(100).

DATA: from_table2(100).

DATA: from_tab(100).

DATA: FIELD_TABLE(20) TYPE C OCCURS 0 WITH HEADER LINE.

DATA: FIELD_TAB(20) TYPE C OCCURS 0 WITH HEADER LINE.

IF input2 NE ''.

CONCATENATE table_1 '~' field2 INTO field_table.

APPEND field_table TO field_tab.

ENDIF.

IF input3 NE ''.

CONCATENATE table_1 '~' field3 INTO field_table.

APPEND field_table TO field_tab.

ENDIF.

IF input4 NE ''.

CONCATENATE table_1 '~' field4 INTO field_table.

APPEND field_table TO field_tab.

ENDIF.

IF input5 NE ''.

CONCATENATE table_1 '~' field5 INTO field_table.

APPEND field_table TO field_tab.

ENDIF.

CONCATENATE table_1 '~' field2 INTO from_table1.

CONCATENATE table_2 '~' field2 INTO from_table2.

CONCATENATE table_1 'INNER JOIN' table_2 'on' from_table1 '=' from_table2 INTO from_tab SEPARATED BY SPACE.

IF table_1 NE '' AND table_2 NE ''.

SELECT (field_tab)

FROM (from_tab)

INTO corresponding fields of table <fs_itab1>

WHERE (where_tab).

ELSEIF table_1 NE ''.

SELECT *

FROM (table_1)

INTO corresponding fields of table <fs_itab1>

where (where_tab).

ENDIF.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

i_structure_name = table_1

TABLES

t_outtab = <fs_itab1>

EXCEPTIONS

program_error = 1

OTHERS = 2.

[Sandra_Rossi]

If you want to create a tool to display joined tables, I recommend you to use quick viewer (transaction sqvi)