topic Re: Dynamic record deletion from database table in Application Development and Automation Discussions

Dynamic record deletion from database table

surya_gupta3 — Thu, 05 Oct 2006 18:58:33 GMT

Hi,

I need to delete selected records from database table(dynamic names). Table names are being passed from main program with some of their field names. The record to be deleted from the database table is being decided based on the fields passed for the table and their contains passed from the main program.

It is not possible to write dynamic where clause for DELETE statement directly.

So, I created a dynamic internal table and i am trying to fetch all records using SELECT statement(for which we can write dynamic where condition, something like...SELECT...WHERE (itab). ) which need to be deleted in the iternal table.

Piece of code :

CONCATENATE c_im v_tablefield1 INTO v_imprtfield1.

CONCATENATE v_tablefield1 c_in v_imprtfield1

into s_condition separated by space.

APPEND s_condition TO t_condition.

PERFORM GET_DYNAMIC_ITAB USING s_flds_agtab-tabname

changing t_itab.

ASSIGN t_itab->* TO <itab>.

*Select the data (to be deleted) from the database table

SELECT * FROM (s_flds_agtab-tabname) INTO TABLE <itab>

WHERE (t_condition).

*Delete the records from the table

IF SY-SUBRC = 0.

DELETE (s_flds_agtab-tabname) FROM TABLE <itab>.

ENDIF.

Here t_condition is of standard table of WHERETXT.

t_condition at the run time before giving dump was:

SPART IN IM_SPART

AND KUNNR IN IM_KUNNR

Here IM_SPART is renge type of SPART and IM_KUNNR is renge of KUNNR.

I am getting a DUMP:

The WHERE condition has an unexpected format.

Error analysis

The current ABAP/4 program attempted to execute an ABAP/4 Open SQL

statement containing a WHERE condition of the form WHERE (itab) or

WHERE ... AND (itab). The part of the WHERE condition specified at

runtime in the internal table itab contains the operator

IN (v1, ..., vn)

in incomplete form.

How to correct the error

If the error occurred in a non-modified SAP program, you may be

able to find a solution in the SAP note system.

If you have access to the note system yourself, use the following

search criteria:

"SAPSQL_IN_ILLEGAL_LIST"

"SAPLZSD_TAB_REFRESH " or "LZSD_TAB_REFRESHU01 "

"Z_SD_REFRESH_AGTABLES"

If you cannot solve the problem yourself, please send the

following documents to SAP:

I would like to know whether "IN" operator is allowed in (itab) of WHERE clause. While testing I changed the "IN" to "=" specifying a suitable value there. It worked. So please let me know if i can give "IN" operator using renge table in the dynamic where clause.

Thanking you,

Surya

Re: Dynamic record deletion from database table

RichHeilman — Thu, 05 Oct 2006 19:03:58 GMT

Someone had this same exact problem last year or so, If my memory serves me correctly, we decided that you can not use the IN operator in a dynamic WHERE clause.

Regards,

Rich Heilman

Re: Dynamic record deletion from database table

RichHeilman — Thu, 05 Oct 2006 19:22:00 GMT

Hi again, so if you can not use the IN in a dynamic where clause you might be forced to dynamically build the entire select statement, Here is a sample program which may give you some ideas, notice that we are writing the select statement code, putting it in another program and generating the subroutine at runtime, then call this routine. I'm sure that this will help you see what you need to do.



report zrich_0003 .

tables: kna1.

types: t_source(72).

data: routine(32) value 'DYNAMIC_SELECT',
             program(8),
             message(128),
             line type i.

data: isource type table of t_source,
            xsource type t_source.

ranges:
        r_kunnr for kna1-kunnr.

data: ikna1 type table of kna1.
data: xkna1 type kna1.


r_kunnr-sign = 'I'.
r_kunnr-option = 'EQ'.
r_kunnr-low    = '0001000500'.
append r_kunnr.

xsource = 'REPORT ZTEMP.'.
insert xsource  into isource index 1.
xsource = 'FORM dynamic_select'.
insert xsource  into isource index 2.
xsource = 'Tables r_kunnr ikna1.'.
append xsource to isource.
xsource = 'select * into table ikna1 from kna1'.
append xsource to isource.
xsource = 'where kunnr in r_kunnr.'.
append xsource to isource.
xsource = 'ENDFORM.'.
append xsource to isource.

generate subroutine pool isource name program
                         message message
                         line line.
if sy-subrc = 0.
  perform (routine) in program (program) tables r_kunnr
                                                ikna1.
else.
  write:/ message.
endif.

loop at ikna1 into xkna1.
  write:/ xkna1-kunnr.
endloop.

Regards,

Rich Heilman

Re: Dynamic record deletion from database table

surya_gupta3 — Thu, 05 Oct 2006 19:28:01 GMT

Hey Rich...So sad to know it.

Could you please suggest me some other alternative.

The requirement is that :

The main program calls a function module passing the internal table(tablename and field name)...like

TAB1 FIELD1

TAB1 FIELD2

TAB1 FIELD3

TAB2 FIELD1

TAB2 FIELD2

TAB3 FIELD1

...

along with the values in range tables.

The containt of internal table passed will not be the same for all the run of this program...so can not think of building static where clause.

Based on the fields passed the table records will be deleted comparing it with the values passed from the program to function module. The passed values are from selection screen, so will be different.

Please give some clue...

Thanking you,

Surya

Re: Dynamic record deletion from database table

surya_gupta3 — Thu, 05 Oct 2006 19:32:57 GMT

Amazing....

Thanks Rich.

Could you please help me once more. How can i reward points in this site. I mean the steps.

Re: Dynamic record deletion from database table

RichHeilman — Thu, 05 Oct 2006 19:36:48 GMT

You should see radiobuttons next to the answers given, here is where you will select to reward points. If the answers are helpful, mark yellow or green, if the answer solved your problem, mark as blue star. Marking as a blue star, says "this question has been answered and it solves my problem", when others are searching for answers in the forums, they can clearly see where the resolutions are.

Regards,

Rich Heilman

Re: Dynamic record deletion from database table

RichHeilman — Thu, 05 Oct 2006 19:45:08 GMT

Of course, now that I'm thinking about, no need to do any select, just simply build your DELETE statement in the generated FORM and call the FORM. Right?

Regards,

Rich Heilman