topic Re: Dynamic Table name in SQL in Application Development and Automation Discussions

Dynamic Table name in SQL

Former Member — Mon, 25 Aug 2008 13:35:01 GMT

I want to give dynamic table name in the SQL query....

select count(*) from (ztabname) into ztabrec.

But the above statement is throwing error...

Please help....

Re: Dynamic Table name in SQL

rainer_hbenthal — Mon, 25 Aug 2008 13:35:52 GMT

And why dont you tell us what error was thrown?

Re: Dynamic Table name in SQL

bpawanchand — Mon, 25 Aug 2008 13:38:04 GMT

TABLES :
   kna1.

PARAMETERS :
    p_tabn(20) TYPE c.

DATA :
   w_name1 TYPE kna1-name1,
   w_land1 TYPE kna1-land1.


SELECT
  name1
  land1
  INTO (w_name1, w_land1)
  FROM (p_tabn)
  UP TO 1 ROWS.
ENDSELECT.

IF w_name1 IS NOT INITIAL.
  WRITE :
   / w_name1.
ENDIF

May be the field names and the table name that you are gving are different or let me know the error ? the above snippet is working fine you can check it out.

Regards

Pavan

Re: Dynamic Table name in SQL

Former Member — Mon, 25 Aug 2008 13:38:44 GMT

Hi, go through the following link,

http://www.susanto.id.au/papers/DynOpenSQL.asp#_Toc23566383

You should declare ZTABNAME as a parameter. At runtime a selection screen will pop up and the user can input the table name.

Re: Dynamic Table name in SQL

Former Member — Mon, 25 Aug 2008 13:40:34 GMT

Hi,

Check this sample code,

DATA:
  o_ref TYPE REF TO data.
FIELD-SYMBOLS:
  <lt_table> TYPE STANDARD TABLE,
  <fs>       TYPE ANY,
  <field>    TYPE ANY,
  <field1>   TYPE ANY.
PARAMETERS:
  p_tab       TYPE tabname,       " Table name (eg: MARA)
  p_field(20) TYPE c.                 " Field name (eg: MATNR)

START-OF-SELECTION.
  CREATE DATA o_ref TYPE TABLE OF (p_tab).

  ASSIGN p_field TO <field1>.
  ASSIGN o_ref->* TO <lt_table>.

  SELECT *
    INTO TABLE <lt_table>
    FROM (p_tab).

  LOOP AT <lt_table> ASSIGNING <fs>.
    ASSIGN COMPONENT <field1> OF STRUCTURE <fs>
                  TO <field>.
    IF sy-subrc = 0.
      WRITE:/ <field>.
    ENDIF.
  ENDLOOP.

"-----------------------------------------------*
"  give input as :
"  p_tab   : mara
"  p_field : matnr.
"-----------------------------------------------*

Regards

Adil

Re: Dynamic Table name in SQL

Former Member — Mon, 25 Aug 2008 14:12:54 GMT

Error :

An exception occurred that is explained in detail below.

The exception, which is assigned to class 'CX_SY_DYNAMIC_OSQL_SYNTAX', was not

caught and

therefore caused a runtime error.

The reason for the exception is:

The running ABAP program attempted to execute an Open SQL statement in

which a FROM clause was specified dynamically in the field

"ZST_TABLES-TAB_NAME". In this

FROM clause, either a table name after a join operator is missing, or an

alias name after the key name "AS". The field "ZST_TABLES-TAB_NAME" could be

empty as

well.

Re: Dynamic Table name in SQL

Former Member — Mon, 25 Aug 2008 14:15:36 GMT

Hi,

Can you show the code.

Regards

Adil

Re: Dynamic Table name in SQL

rainer_hbenthal — Mon, 25 Aug 2008 14:39:09 GMT

if you do not use a standard report or any other standard functionality from sap, you can export your data as csv without having this limitation.

Re: Dynamic Table name in SQL

Former Member — Mon, 01 Sep 2008 09:15:13 GMT

Hi,

Please check the sample code below, it serves the same requirement as that of yours,

TABLES : tadir. " table in which the obj_name field stores all the

" database table names

TYPE-POOLS : slis.

TYPES ztab LIKE dcobjdef-name .

DATA : dyntab LIKE dntab OCCURS 0 WITH HEADER LINE,

dref TYPE REF TO data,

i_fcat TYPE lvc_t_fcat ,

wa_fcat TYPE lvc_s_fcat ,

w_pgm LIKE sy-repid,

count TYPE i VALUE 0,

w_obj_name LIKE tadir-obj_name.

DATA : wa_fieldcat TYPE slis_fieldcat_alv, "ALV FIELD CATALOG TABLE

i_fieldcat TYPE slis_t_fieldcat_alv, "ALV FIELD CATALOG STRUCTURE

i_event TYPE slis_t_event, "ALV EVENT HANDLING TABLE

wa_event TYPE slis_alv_event, "ALV EVENT HANDLING STRUCT

i_listheader TYPE slis_t_listheader, "ALV LIST HEADER TABLE

wa_listheader TYPE slis_listheader, "ALV LIST HEADER STRUCT

i_layout TYPE slis_layout_alv.

FIELD-SYMBOLS :<newtab> TYPE table.

INITIALIZATION.

w_pgm = sy-repid.

****************SELECTION-SCREEN 2000

SELECTION-SCREEN BEGIN OF SCREEN 2000 AS WINDOW.

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE tit1.

PARAMETERS: tab_name TYPE ztab . " Enter database table name

SELECTION-SCREEN END OF BLOCK b1.

SELECTION-SCREEN END OF SCREEN 2000.

***************END OF SELECTION-SCREEN 2000

tit1 = 'Enter Table Name?'.

START-OF-SELECTION.

CALL SELECTION-SCREEN 2000 STARTING AT 10 10.

PERFORM occurcheck.

*********CHECKING WHETHER THE DATABASE TABLE EXISTS

FORM occurcheck.

SELECT obj_name FROM tadir INTO w_obj_name WHERE obj_name = tab_name.

ENDSELECT.

IF sy-subrc <> 0.

MESSAGE i003."Table not found in the database .

CALL SELECTION-SCREEN 2000 STARTING AT 10 10.

PERFORM occurcheck.

ELSE.

PERFORM startprocess.

EXIT.

ENDIF.

ENDFORM.

**********GETTING THE FIELD NAMES OF THE INPUT DATABASE TABLE

FORM startprocess.

CALL FUNCTION 'NAMETAB_GET'

EXPORTING

langu = sy-langu

tabname = tab_name

TABLES

nametab = dyntab. "dntab now contains the field names

**********FILLING THE CATALOG OF NEW DYNAMIC INTERNAL TABLE

LOOP AT dyntab.

wa_fcat-fieldname = dyntab-fieldname.

wa_fcat-ref_field = dyntab-fieldname.

wa_fcat-ref_table = dyntab-tabname.

APPEND wa_fcat TO i_fcat .

ENDLOOP.

***********CREATING A POINTER (FIELD SYMBOL) TO THE INTERNAL TABLE

CALL METHOD cl_alv_table_create=>create_dynamic_table

EXPORTING

it_fieldcatalog = i_fcat

IMPORTING

ep_table = dref.

ASSIGN dref->* TO <newtab>.

SELECT * FROM (dyntab-tabname) INTO TABLE <newtab>.

**********CHECKING WHETHER DATA HAS BEEN UPLOADED.

DESCRIBE TABLE <newtab> LINES sy-tfill.

DESCRIBE TABLE <newtab> LINES count.

IF sy-tfill = 0 .

MESSAGE i001. "Internal table is not filled

ELSE.

MESSAGE i002 WITH count. "Internal table is filled

ENDIF.

****************EVENTS USED IN ALV

CALL FUNCTION 'REUSE_ALV_EVENTS_GET'

EXPORTING

i_list_type = 0 " 0-simple list, 1-hierarchial list.

IMPORTING

et_events = i_event.

SORT i_event.

READ TABLE i_event

WITH KEY name = slis_ev_top_of_page "TOP_OF_PAGE event

INTO wa_event.

IF sy-subrc = 0.

MOVE 'IAM_TOP_OF_PAGE' TO wa_event-form. "IAM_TOP_OF_PAGE will

"call subroutine automatically

MODIFY i_event FROM wa_event INDEX sy-tabix.

ENDIF.

****************DISPLAYING ALV.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

i_callback_program = w_pgm

i_structure_name = dyntab-tabname

it_events = i_event

TABLES

t_outtab = <newtab>.

ENDFORM.

**********FORM TOP_OF-PAGE.

******FILLING OF LIST HEADER TABLE

FORM iam_top_of_page.

CLEAR wa_listheader.

wa_listheader-typ = 'H'. "HEADING

wa_listheader-info = 'ALV CREATED DYNAMICALLY'.