topic Re: Dynamic Open SQL Question in Application Development and Automation Discussions

Dynamic Open SQL Question

Former Member — Wed, 04 Apr 2007 10:26:15 GMT

Hi Experts,

I'm working on a programme in which, tablename, fieldname and the where conditions are all specified on-the-fly i.e. dynamically, even the type for the field is unknown. I've searched through some tips in this forum and some example given fetches the whole line from the database. Obviously this is what I intend to avoid.

Could anyone show me the rope on how to write such a dynamic sql statement? Thanks.

Re: Dynamic Open SQL Question

Former Member — Wed, 04 Apr 2007 10:36:41 GMT

Hi James,

May be this helps:

data: g_fld(20), g_tbl(20), g_wre(20).

g_fld = 'matnr makt'.

g_tbl = 'maktx'.

g_wre = 'matnr = g_matnr'.

select (g_fld) from (g_tbl) where (g_wre).

"Process you data here

endselect.

Reward if it helps.

Re: Dynamic Open SQL Question

Former Member — Wed, 04 Apr 2007 10:37:25 GMT

You can do something like

select (fields)

from (table)

where (condition)

You may need to create an internal table dynamically aswell.

Re: Dynamic Open SQL Question

Former Member — Wed, 04 Apr 2007 10:41:56 GMT

But in this way, obviously I have to write a bunch of if-elses to define variables of different types to hold the search results, because I dont know the field's type. If it's an int, I would write: data: fld type int. suppose it's a count(field) select, it probably looks like: select (fld) into fld. if it's a string, then I would define a string type to hold the value. This is also a question for where conditions when I concatenate the strings because the types are unknown.

Re: Dynamic Open SQL Question

Former Member — Wed, 04 Apr 2007 10:46:08 GMT

Hi Martin,

Could you give me some examples to achieve the goal? Since I'm not quite familiar with the dynamic tech.

Re: Dynamic Open SQL Question

Former Member — Wed, 04 Apr 2007 10:52:18 GMT

Hello James,

I have written some code in my pgm. Check it below :

(I have maintained a table which has a structure like :

TABLE1 (table name)

FIELD1 (Field in the table)

SELECTION ( If this field is a Sel option / can be selected )

________________

dynmaische where_tab

l_fieldname-sign = 'I'.

l_fieldname-option = 'EQ'.

(Gtab contains the Sel Info)

LOOP AT gtab_map INTO l_str_map

WHERE table1 = gcon_table_vtbfha

AND selection = gcon_xon.

l_fieldname-low = l_str_map-field1.

APPEND l_fieldname TO l_range_vtbfha.

ENDLOOP.

where_tab aufbauen

CALL FUNCTION 'FREE_SELECTIONS_RANGE_2_WHERE'

EXPORTING

field_ranges = l_tab_trange

IMPORTING

where_clauses = l_tab_where_clauses.

READ TABLE l_tab_where_clauses INTO l_str_where_clauses

WITH KEY tablename = gcon_table_vtbfha.

c_where_vtbfha = l_str_where_clauses-where_tab.

Re: Dynamic Open SQL Question

Former Member — Wed, 04 Apr 2007 10:58:16 GMT

Hi Srivijaya,

I'm not sure about your code because I dont see where you generate the sql statement.

Basically I think to achieve this goal, it should involve some defined field symbol with some data assigned to it, this is the example I searched in this forum:

DATA: a_table_line TYPE REF TO data.

DATA: table_lines TYPE STANDARD TABLE OF REF TO data.

DATA: c TYPE cursor.

FIELD-SYMBOLS: <line> TYPE ANY.

FIELD-SYMBOLS: <field> TYPE ANY.

PARAMETERS: p_tab TYPE dd02l-tabname.

START-OF-SELECTION.

OPEN CURSOR c FOR SELECT * FROM (p_tab)

ORDER BY PRIMARY KEY.

DO.

CREATE DATA a_table_line TYPE (p_tab).

ASSIGN a_table_line->* TO <line>.

FETCH NEXT CURSOR c INTO <line>.

IF sy-subrc NE 0.

CLOSE CURSOR c.

EXIT.

ENDIF.

APPEND a_table_line TO table_lines.

ENDDO.

LOOP AT table_lines INTO a_table_line.

ASSIGN a_table_line->* TO <line>.

NEW-LINE.

DO 6 TIMES.

CHECK sy-index > 1.

ASSIGN COMPONENT sy-index OF STRUCTURE <line> TO <field>.

IF sy-subrc NE 0.

EXIT.

ENDIF.

WRITE: <field>.

ENDDO.

ENDLOOP.

Unfortunately, it searches the whole line in the DB while I only require 1 field, I have no idea how to modify this programme to suit my need. Thanks

Re: Dynamic Open SQL Question

Former Member — Wed, 04 Apr 2007 11:10:48 GMT

Hi james,

Try this :

Create a new Table

CALL METHOD cl_alv_table_create=>create_dynamic_table

EXPORTING

it_fieldcatalog = IT_LVC_CAT

IMPORTING

ep_table = new_table.

With this u can create a dymanic table and also refer this wiki

https://www.sdn.sap.com/irj/sdn/wiki?path=/display/snippets/dynamic%2binternal%2btable

You will get idea how to create dynamic itab. With this you need not to create sql using if ..then.. and no pro of fld type.

Hope this will work.

Re: Dynamic Open SQL Question

Former Member — Wed, 04 Apr 2007 11:17:50 GMT

add all the Sel options to l_tab_trange .

For building dynamic where clauses use the function.

DATA:

c_where TYPE rsds_where_tab

CALL FUNCTION 'FREE_SELECTIONS_RANGE_2_WHERE'

EXPORTING

field_ranges = l_tab_trange

IMPORTING

where_clauses = l_tab_where_clauses.

READ TABLE l_tab_where_clauses INTO l_str_where_clauses

WITH KEY tablename = TABLE.

c_where = l_str_where_clauses-where_tab.

CREATE A DYN TABLE

CALL METHOD cl_alv_table_create=>create_dynamic_table

EXPORTING

it_fieldcatalog = ifc

IMPORTING

ep_table = dy_table.

ASSIGN dy_table->* TO <itab>.

SELECT (fieLd1) FROM (p_tab) INTO CORRESPONDING FIELDS OF TABLE

<ITAB> WHERE (u_where).

I GUESS .. THIS SHLD BE THE APPROACH.

Re: Dynamic Open SQL Question

Former Member — Wed, 04 Apr 2007 11:30:01 GMT

CHECK THIS IF HELPFUL

http://help.sap.com/saphelp_nw04/helpdata/en/9d/05b8d660f77f4387d6d054d7655ecb/frameset.htm

Re: Dynamic Open SQL Question

Former Member — Wed, 04 Apr 2007 11:35:57 GMT

Hi,

Sorry that I forgot to mention my programme needs to support ABAP 46C as well, not sure if RTTI could be used.