topic Re: Table name decided at runtime - How to do a select? in Application Development and Automation Discussions

Table name decided at runtime - How to do a select?

Former Member — Thu, 05 Feb 2009 12:43:30 GMT

Hi,

Searching these forums I found how to do a select * from where the table name is decided at runtime. You use paranthesis on the variable containing the table name.

However, I still get syntax error I can't solve and need some help.

This syntax doesnt work, could you help me find out what is the problem?

SELECT tab1~field1 tab2~field2
FROM ( (dynamic_tab_name1) AS tab1 INNER JOIN
       (dynamic_tab_name2) AS tab2 ON tab1~someIndex = tab2~someIndex )

dynamic_tab_name1 and dynamic_tab_name2 are defined as two strings where I concatenate the table prefix name with the system client number.

The syntax works if i replace the dynamic_tab_name1 and dynamic_tab_name2 with hardcoded table names.

thanks & regards

Edited by: Baran Sölen on Feb 5, 2009 1:44 PM

Re: Table name decided at runtime - How to do a select?

Former Member — Thu, 05 Feb 2009 12:46:39 GMT

Hi,

Please find the Code below

DATA wa TYPE scarr.

DATA name(10) TYPE c VALUE 'SCARR'.

SELECT *

INTO wa

FROM (name) CLIENT SPECIFIED

WHERE mandt = '000'.

WRITE: / wa-carrid, wa-carrname.

ENDSELECT.

[Link|http://help.sap.com/saphelp_47x200/helpdata/en/c6/617cf4e68c11d2b2ab080009b43351/content.htm]

Hope this is help Full

Thanks

kalyan

Re: Table name decided at runtime - How to do a select?

Former Member — Thu, 05 Feb 2009 12:51:01 GMT

Hi Baran

Try this


FIELD-SYMBOLS : <FS1> TYPE TABLE, <FS2> TYPE TABLE.

ASSIGN (dynamic_tab_name1) TO <FS1>.
ASSIGN (dynamic_tab_name2) TO <FS2>.

SELECT tab1~field1 tab2~field2
FROM ( <FS1> AS tab1 INNER JOIN
       <FS1> AS tab2 ON tab1~someIndex = tab2~someIndex )

Pushpraj

Re: Table name decided at runtime - How to do a select?

ThomasZloch — Thu, 05 Feb 2009 12:55:01 GMT

You would have to put the entire JOIN syntax into a variable, see the example here:

http://help.sap.com/abapdocu/en/ABAPFROM_CLAUSE.htm#!ABAP_ALTERNATIVE_3@3@

Thomas

Re: Table name decided at runtime - How to do a select?

viquar_iqbal — Thu, 05 Feb 2009 12:55:19 GMT

Also you can move the value of the dynamic table into a character data object and then use them in the Join condition.

Thanks

Viquar Iqbal

Re: Table name decided at runtime - How to do a select?

Former Member — Thu, 05 Feb 2009 13:21:20 GMT

Hi,

Pushpraj Singh : That didnt work either. If you try that you will get the syntax error (try yourself):

<FS1> is not defined in the ABAP dictionary as a table, projection, view or database view.

Re: Table name decided at runtime - How to do a select?

Former Member — Thu, 05 Feb 2009 13:27:23 GMT

Hi,

Instead when you have dynamic tables you can go for for all entires and

can implement the logic of inner join.

For example check the below sample code,

Here to aboid inner join of mara and marc i ve used for all entries

DATA: it_tab TYPE TABLE OF ty_tab,
      it_matnr TYPE TABLE OF ty_matnr,
      wa_tab TYPE ty_tab,
      wa_matnr TYPE ty_matnr.

DATA: w_tab1 TYPE tabname,
      w_tab2 TYPE tabname.

w_tab1 = 'MARA'.
w_tab2 = 'MARC'.

DATA: BEGIN OF it_fields OCCURS 0,
      field TYPE fieldname,
      END OF it_fields.

it_fields-field = 'MATNR'.
APPEND it_fields.




SELECT (it_fields) UP TO 10 ROWS
       INTO TABLE it_matnr
       FROM (w_tab1).

REFRESH it_fields[].

it_fields-field = 'MATNR'.
APPEND it_fields.

it_fields-field = 'WERKS'.
APPEND it_fields.

SELECT (it_fields) UP TO 10 ROWS
       INTO TABLE it_tab
        FROM (w_tab2)
       FOR ALL ENTRIES IN it_matnr
       WHERE matnr = it_matnr-matnr.

Hope this helps you.

Regards,

Manoj Kumar P

Re: Table name decided at runtime - How to do a select?

Former Member — Thu, 05 Feb 2009 13:45:54 GMT

Hi Baran

Try FIELD-SYMBOLS : <FS1> TYPE ANY.

Pushpraj

Re: Table name decided at runtime - How to do a select?

ThomasZloch — Thu, 05 Feb 2009 13:48:26 GMT

Doesn't work with field-symbols, declaration does not matter.

Thomas

Re: Table name decided at runtime - How to do a select?

Former Member — Thu, 05 Feb 2009 14:01:31 GMT

Hi,

Thomas: I have read the example you've sent me. Unfortunately there is problem with that example.

I put everything in a string like the example but then noticed one thing (and this is probably an easy thing for experienced programmers to solve):

The table names they use in the string are not decided at runtime but that table scarr they use as an example is a table defined in the program. I get a runtime error ( cx_sy_dynamic_osql_semantics) when trying to do this:

from_syntax = `( (dynamic_tabname1) AS tab1`
                & ` INNER JOIN (dynamic_tabname2) AS tab2`
                & ` ON tab1~someIndex = oph~someIndex ).`.


SELECT tab1~field1 tab2~field2
FROM (from_syntax)

This is for me hovever not strange. And again, in the example they have "scarr" as an existing table where in my case i dont know the tablename in advance but set the string dynamic_tabname1 during runtime.

Again, can anyone write any code with INNER JOINS where the table names are set during runtime?!

Edited by: Baran Sölen on Feb 5, 2009 3:02 PM

Edited by: Baran Sölen on Feb 5, 2009 3:05 PM

Re: Table name decided at runtime - How to do a select?

ThomasZloch — Thu, 05 Feb 2009 14:18:20 GMT

Try concatenating the value of dbtab_syntax from your variables and the literals inbetween.

e.g.

CONCATENATE dynamic_tabname1 ' AS tab1 JOIN ' dynamic_tabname2 ' AS tab2 ON ... ' INTO dbtab_syntax.

Thomas

Re: Table name decided at runtime - How to do a select?

Former Member — Thu, 05 Feb 2009 14:26:05 GMT

Hello Baran,

1. Create a parameter,

PARAMETERS:

p_tbname type dd02l-tabname.

This will provide F4-help to choose database table, since DD02L contains all database table names.

2. Create a table exactly like the structure of chosen table,

DATA T_TABLE LIKE STANDARD TABLE OF P_TBNAME WITH HEADER LINE.

3. Run a select query,

select *

from (p_tbname)

into table t_table.

Try doing different test-cases.

Thankyou,

Zahack.

Re: Table name decided at runtime - How to do a select?

Former Member — Thu, 05 Feb 2009 14:28:08 GMT

Hi,

Have you tried converting your for join logic to for all entries logic as I've mentioned above?

Re: Table name decided at runtime - How to do a select?

Former Member — Thu, 05 Feb 2009 14:35:16 GMT

Well you can generate your program dynamically in the following way.

But I never like to use the method.



append 'PROGRAM SUBPOOL no standard page heading.' to code.
append 'form dyn1.' to code.
append 'data: begin of i_tab occurs 0,' to code.
append '      kolnr like t682i-kolnr,' to code.
append '      gstxt like tmc1t-gstxt,' to code.
append '      kschl like t685-kschl,' to code.
append '      vkorg like komg-vkorg,' to code.
append '      vtweg like komg-vtweg,' to code.
append '      matkl like komg-matkl,' to code.
append '      matnr like komg-matnr,' to code.
append '      inco1 like komg-inco1,' to code.
append '      inco2 like komg-inco2,' to code.
append '      charg like komg-charg,' to code.
append '      vkbur like komg-vkbur,' to code.
append '      vkgrp like komg-vkgrp,' to code.
append '      zsales like komg-zsales,' to code.
append '      end of i_tab.' to code.

append 'data: begin of i_t682 occurs 0,' to code.
append '      kolnr like t682i-kolnr,' to code.
append '      kotabnr like t682i-kotabnr,' to code.
append '      end of i_t682.' to code.

data: v_table_name(16),
      v_condition_type_code(72),
      v_kolnr like t682i-kolnr.

concatenate 'kschl = ''' p_kschl  '''.' into v_condition_type_code.
  loop at i_t682.

    concatenate 'A' i_t682-kotabnr into v_table_name.
    append 'select * from' to code.
    append v_table_name to code.
    append 'appending corresponding fields of table i_tab' to code.
    append 'where kappl = ''V'' and' to code.
    append v_condition_type_code to code.
    append 'if sy-subrc = 0.' to code.
    append 'loop at i_tab where kolnr = '' ''.' to code.
    concatenate 'i_tab-kolnr = ' i_t682-kolnr '.' into code separated by space.
    append code. clear code.
    append 'i_tab-gstxt = ''Description will be put later''.' to code.
    append 'modify i_tab.' to code.
    append 'endloop.' to code.
    append 'endif.' to code.

  endloop.
append 'export i_tab to memory id ''i_tab''.' to code.
append 'endform.' to code.
generate subroutine pool code
         name prog
         message msg
         line lin
         word wrd
         offset off.
*break-point.
if sy-subrc = 0.
  perform dyn1 in program (prog).
endif.
import i_tab from memory id 'i_tab'.

Re: Table name decided at runtime - How to do a select?

Former Member — Thu, 05 Feb 2009 15:09:17 GMT

Many thanks to helpful answers.

I tried Thomas tip first and it worked, thank you.

Thanks Manoj for the useful tip, that is an option as well.