topic Open SQL statment for Update flag based on Date in Application Development and Automation Discussions

Open SQL statment for Update flag based on Date

Former Member — Tue, 07 Apr 2009 06:22:47 GMT

Dear all,

I am trying to write an Open SQL statement to update a flag in a table. Table Ztable1 with fields Sr.No, Flag, Datefrom, DateTo. i would like to update Flag entry in the table only if today falls in between Datefrom & Dateto. I can satisfy the above requirement using the following ABAP code.


DATA: lv_timestamp TYPE timestamp,
      lv_today LIKE adr2-valid_from,
      tz TYPE timezone.

CONVERT DATE sy-datlo TIME sy-timlo INTO TIME STAMP lv_timestamp
  TIME ZONE tz.
lv_today = lv_timestamp.

update ztable1 set flag = 'X' where lv_today BETWEEN datefrom and dateto.

But the issue is that, DateFrom & DateTo contains space aswell Dates. Datefrom can be space if it is start of Time (01010001) and also DateTo can be space if it is End of time (31129999). Which means that if DateFrom is space, then it should treated as 01010001, simlarly DateTo is space, then it should be 31129999. How can i write the if else cases within where clauses.

I know Decode statement in Native sql programming, but that won't fit for Opensql in ABAP. Also, because of huge entries in database, i cannot read entries, manupulate & then update.

How can i enhance the same above Update statement to cater this need.

Please advise.

Thanks a lot in advance.

Greetings, Satish

Re: Open SQL statment for Update flag based on Date

Peranandam — Tue, 07 Apr 2009 07:00:12 GMT

Hi,

first fetch records in to internal table.

ranges: r_range for sy-datum.

loop at itab into wa.

if wa-validfrom is initial.

wa-validfrom = (here u pass valid from date).

elseif wa-validto is initial

wa-validto = 99991231.

endif.

r_range-low = wa-validfrom

r_range-high = wa-validto

*check here current date is falling in interval. if its fall between ranges update flas in work area and *modify you internal table

if sy-datum in r_range.

wa-flag = 'x'.

modify itab from wa.

endif.

refresh r_range.

clear wa-flag.

endloop.

*--Finally update your ztable

modify ztable from table itab.

Regards,

Peranandam

Re: Open SQL statment for Update flag based on Date

Former Member — Wed, 13 Jul 2011 17:40:12 GMT

resolved myself.