topic Re: Dynamic SQl Query Select in Application Development and Automation Discussions

Dynamic SQl Query Select

Former Member — Thu, 06 May 2010 10:49:52 GMT

Hello Colleague,

I have a table that contains multiple entries of TABLE NAME.

I need to query the databse table and check if certain entry exist in that table.

I Loop over the table containing the tablenames and try a select :

Loop at lt_key into ls_key.

SELECT * FROM (ls_key) WHERE field = 'ABC' .

ENDLOOP.

It gives me a error and asks to store the result using INTO. Now i am confused with the type of result because the tablename is provided dynamically.

Could you please help.

Thanks in advance.

Piyush

Re: Dynamic SQl Query Select

agnihotro_sinha2 — Thu, 06 May 2010 10:53:35 GMT

Hi,

Pass the name of the Table to the Field symbol.

ASSIGN (ls_key) TO <fs_tab>.

and then use this FS to get the data in SELECT statement

ags.

Re: Dynamic SQl Query Select

Former Member — Thu, 06 May 2010 10:54:30 GMT

HI,

I did something similar to it, the following program accepts table

name and fields from user and sort dynamically.


REPORT  zreport_SORT.

parameters:
  p_f1(15) obligatory,
  p_f2(15) obligatory,
  p_f3(15) obligatory,
  p_table(15).

data:
  w_tab type ref to data,
  fs_tab type ref to data,
  w_flag,
  w_count type i.


create data w_tab type table of  (p_table) .
create data fs_tab type (p_table).

data:
  t_tab type table of spfli,
  fs_tab1 like line of t_tab.

field-symbols:
  <fs> type any table,
  <fs1> type any,
  <fs2> type any,
  <fs3> type any,
  <fs4> type any.

at selection-screen.

select count(*)
  from dd03l
  into w_count
 where tabname eq p_table
   and fieldname eq p_f1.
if w_count eq 0.
  w_flag = 'Y'.
 endif.
select count(*)
  from dd03l
  into w_count
 where tabname eq p_table
   and fieldname eq p_f2.

if w_count eq 0.
  w_flag = 'Y'.
 endif.

select count(*)
  from dd03l
  into w_count
 where tabname eq p_table
   and fieldname eq p_f3.

if w_count eq 0.
  w_flag = 'Y'.
 endif.
if w_flag eq 'Y'.
 clear w_flag.
 Message 'Incorrect field name' type 'E'.
endif.

start-of-selection.
assign w_tab->* to <fs>.
assign fs_tab->* to <fs1>.

  select *
    from (p_table)
    into corresponding fields of
   table <fs> up to 10 rows.
   sort <fs> by (p_f1) ascending (p_f2) ascending (p_f3) ascending.

assign component p_f1 of structure <fs1> to <fs2>.
assign component p_f2 of structure <fs1> to <fs3>.
assign component p_f3 of structure <fs1> to <fs4>.


  loop at <fs> into <fs1>.
  write:/ <fs2>,
          <fs3>,
          <fs4>.
  endloop.

Regards and Best wishes.

Re: Dynamic SQl Query Select

SuhaSaha — Thu, 06 May 2010 10:55:55 GMT

Why do you need this SELECT stmt ? Do you want to use the data selected from the table or just want to check if there are any entries in the table with field = 'ABC' ?

If you want to use the data:

DATA:
DREF TYPE REF TO DATA.

FIELD-SYMBOLS:
<ITAB> TYPE STANDARD TABLE.


LOOP AT ITAB INTO LS_KEY.

CREATE DATA DREF TYPE STANDARD TABLE OF (ls_key).
ASSIGN DREF->* TO <ITAB>.

SELECT * FROM (ls_key) WHERE field = 'ABC' INTO TABLE <ITAB>.

ENDLOOP.