topic Dynamic select sql statement in Application Development and Automation Discussions

Dynamic select sql statement

Former Member — Thu, 03 Jan 2008 02:25:52 GMT

Hi currently i have a problem with the sql statement.

The field name and the table name of the sql statement will depend on what the user input. We need to find out what records is in tableA columnA and not in tableB columnB.

Below is my sql statement:

SELECT * FROM (TABLEA) INTO TABLE ITABA

WHERE (COLUMNA)

NOT IN ( SELECT (COLUMNB) FROM (TABLEB) ).

=============================================

ColumnA = "the user input the field name"

TableA = " the user input the table name"

TableB = " the user input the table name"

The problem lies at the WHERE clause. The system generate a sql error which says "unable to find the field name".

Re: Dynamic select sql statement

Former Member — Thu, 03 Jan 2008 02:30:00 GMT

Refer class CL_ABAP_TYPEDESCR.

It is used to create the dynamic SQL.

Also search the forums. There are already lots of thread on this topic.

Regards,

Atish

Re: Dynamic select sql statement

Former Member — Thu, 03 Jan 2008 02:44:24 GMT

Hi thanks, but how do i use the class?

Re: Dynamic select sql statement

Former Member — Thu, 03 Jan 2008 11:47:26 GMT

Hi Jocelyn,

Following is the code which i used for a dynamic SQL Query and it worked for me.

DATA where_tab(25) OCCURS 5 WITH HEADER LINE.

DATA p_kunnar like vbak-kunnr.

CONDENSE p_kunnar NO-GAPS.

IF p_kunnar NE ''.

APPEND 'vbak~kunnr = p_kunnar' TO where_tab.

ENDIF.

SELECT DISTINCT vbeln

FROM vbak

INTO CORRESPONDING FIELDS OF TABLE gi_vtab

WHERE vbak-vkorg = p_vkorg AND

vbak-vtweg = p_vtweg AND

vbak-auart = 'LP' AND

(where_tab).

Regds

Satya

Provide points if the answer is helpful..*

Re: Dynamic select sql statement

Former Member — Thu, 03 Jan 2008 11:51:40 GMT

Hi,

Check the following code:

For dynamic table name....

REPORT demo_select_dynamic_database .

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.

*-----

For dynamic field list

REPORT demo_select_dynamic_columns .

DATA: itab TYPE STANDARD TABLE OF spfli,

wa LIKE LINE OF itab.

DATA: line(72) TYPE c,

list LIKE TABLE OF line(72).

line = ' CITYFROM CITYTO '.

APPEND line TO list.

SELECT DISTINCT (list)

INTO CORRESPONDING FIELDS OF TABLE itab

FROM spfli.

IF sy-subrc EQ 0.

LOOP AT itab INTO wa.

WRITE: / wa-cityfrom, wa-cityto.

ENDLOOP.

ENDIF.

Regards,

Bhaskar