topic Re: Dynamic Tables & SQL Statements in Application Development and Automation Discussions

Dynamic Tables & SQL Statements

Former Member — Wed, 16 Jan 2008 05:22:18 GMT

Example:

A user types in a SQL statement, " Country = 'US' ", into a input field. i read it and copy into table A. how am i supposed to use the statement found in the table to do a comparison of data from table B? i've found a example in the abap documentation but i'm not sure how to retrieve the statement from table A. please provide sample codes so i know how it works. i'll reward handsomely if the answer's close (((((((:

Thanks (:

REPORT demo_special_tech_subroutine_1.

DATA: code TYPE TABLE OF rssource-line,

prog(8) TYPE c, msg(120) TYPE c, lin(3) TYPE c,

wrd(10) TYPE c, off(3) TYPE c.

APPEND 'PROGRAM SUBPOOL.'

TO code.

APPEND 'FORM DYN1.'

TO code.

APPEND

'WRITE / ''Hello, I am the temporary subroutine DYN1!''.' "#EC NOTEXT

TO code.

APPEND 'ENDFORM.'

TO code.

APPEND 'FORM DYN2.'

TO code.

APPEND

'WRIT / ''Hello, I am the temporary subroutine DYN2!''.' "#EC NOTEXT

TO code.

APPEND 'ENDFORM.'

TO code.

GENERATE SUBROUTINE POOL code NAME prog

MESSAGE msg

LINE lin

WORD wrd

OFFSET off.

IF sy-subrc <> 0.

WRITE: / 'Error during generation in line', lin, "#EC NOTEXT

/ msg,

/ 'Word:', wrd, 'at offset', off. "#EC NOTEXT

ELSE.

WRITE: / 'The name of the subroutine pool is', prog. "#EC NOTEXT

SKIP 2.

PERFORM dyn1 IN PROGRAM (prog).

SKIP 2.

PERFORM dyn2 IN PROGRAM (prog).

ENDIF.

Edited by: Leslie Koh on Jan 16, 2008 6:31 AM

Re: Dynamic Tables & SQL Statements

Former Member — Wed, 16 Jan 2008 05:47:50 GMT

Hi Leslie

If my understanding is right your requirement is to pass 'Country = 'US'' to the select condition for another table, below example can help you understand in handling the scenario.


data: l_land1 type land1.

data: code(20) type c.

move 'LAND1 = ''US''' TO code.
select single land1 into l_land1 from t005
       where (code)
       and   spras = sy-langu.

write:/ l_land1.

Kind Regards

Eswar

Re: Dynamic Tables & SQL Statements

Former Member — Wed, 16 Jan 2008 06:00:43 GMT

hi Eswar Rao Boddeti,

thanks for your fast response but i'm trying to do it dynamic, meaning you will not know what the user key in for the SQL statement. i need to run a program to retrieve data from table A, using a loop case, and based on the SQL statement found at table A to compare the data from table B.

what you're doing is hard-coding the 'us' into code. but thanks again for your help (: i've rewarded you (: please continue your help

Re: Dynamic Tables & SQL Statements

Former Member — Wed, 16 Jan 2008 06:09:13 GMT

Hi Leslie

Thought the code can give you a lead to handle.

Anyhow check below example:



types: begin of ty_code,
         text type char255,
       end of ty_code.
data: it_code type table of ty_code,
      wa_code type ty_code.

select * from <tabA> into <itabA>.

loop at <itabA> into <waA>.
     move <waA>-cfield to wa_code-text.
     append wa_code to it_code.
endloop.

* Now the conditions exists in table it_code, Use the same for next select

select * from <tabB> into <itabB>
       where (it_code).

Kind Regards

Eswar

Re: Dynamic Tables & SQL Statements

Former Member — Wed, 16 Jan 2008 06:23:39 GMT

Hi Eswar,

the codes you provide is very helpful. but the thing is that i'm not sure abt what's ty_code, it_code and <waA>-cfield.

thanks (:

Re: Dynamic Tables & SQL Statements

Former Member — Wed, 16 Jan 2008 06:33:40 GMT

Leslie

Check below comments:



types: begin of ty_code,   " Type declaration
         text type char255,
       end of ty_code.

data: it_code type table of ty_code, " Internal table to hold the dynamic WHERE condition
      wa_code type ty_code. " Work Area
 
select * from <tabA> into <itabA>. " tabA is the first table which holds the SQL conditions
 
loop at <itabA> into <waA>. 
     move <waA>-cfield to wa_code-text. " In this example as presuming CFIELD has the SQL condition
     append wa_code to it_code. " Am preparing an internal table with all the conditions
endloop.

* Note that if you have more than one condition you may need to add AND to starting 2nd line
 
* Now the conditions exists in table it_code, Use the same for next select
 
select * from <tabB> into <itabB>
       where (it_code). " Retreiving data from table tabB with the SQL conditions from tabA

Hope this helps you understand.

Try using the above code in a temporary program, replace <tabA> with your first table that holds SQL conditions, <tabB> with the second table.

Kind Regards

Eswar

Re: Dynamic Tables & SQL Statements

Former Member — Wed, 16 Jan 2008 06:56:52 GMT

loop at <itabA> into <waA>.

where does the <waA> come from?

thanks ((:

Re: Dynamic Tables & SQL Statements

Former Member — Wed, 16 Jan 2008 07:02:47 GMT

<itabA> and <waA> are the internal table and work area for the first table, Similiar is the case for table 2 with <itabB> and <waB>.

Regards

Eswar

Re: Dynamic Tables & SQL Statements

Former Member — Wed, 16 Jan 2008 07:19:06 GMT

thanks eswar (: you've solved my problem!!! (((: