Batch Program - Need to write Select Query

[Former Member]

Hello Experts,

  I need to Query one Table which has two fields : QUOTE_STATUS and Quote_Submitted_at_time.

in my Batch Program which runs every 15 min, i need to write a select Query to fetch all the Quotes Submited During this 15 Min Gap and for a particular status.  Please suggest a Performance efficient Select Query for this.

Thanks and Regards,

Kaushik

[FredericGirod]

Hi,

give us the structure of your table

regards

Fred

[Former Member]

Hello Frédéric Girod ,

STATUS                     Type   J_TXT04 ,

ZZSUBMITTED_AT   Type   SYUZEIT.

  Now i need to get all the Entries in the table where the status is "XYZ" and time frame is from Past 15 minutes which all Quotes are Updated (Ie ZZSUBMITTED_AT ).

Example : Suppose now the time is 10.30 AM, i need to get all the entries from the table from 10.15 AM to 10.30 AM where in  ZZSUBMITTED_AT is updated.

Bascially  ZZSUBMITTED_AT is a time field which is used to check when the entries in the table is last modified.

Thanks and Regards,

Nikhil Kulkarni

[venkat_aileni]

Hi-

Try this:

DATA: lv_time TYPE sy-uzeit,

       lv_time2 TYPE sy-uzeit.

lv_time = sy-uzeit.

lv_time2 = '001500'.

WRITE: lv_time.

lv_time = lv_time - lv_time2.

WRITE: lv_time.

After last step field lv_time will have system time - 15 minutes. Now you can query your table where the status is 'XYZ' and time frame from lv_date.

-Venkat

[Former Member]

Hello Venkat,

Can you paste the select Query for completing this answer.

Thanks alot,

BR,

Kaushik

[venkat_aileni]

Sure!

DATA: lv_time TYPE sy-uzeit,

       lv_time2 TYPE sy-uzeit.

lv_time = sy-uzeit.

lv_time2 = '001500'.

lv_time = lv_time - lv_time2.

select *

from <your table name>

into table it_final

where status = 'XYZ'

  and zzsubmitted_at GE lv_time.

Thanks.

-Venkat

[nabheetscn]

You can do as follow.

creat a range for time.

data:lr_time type range of sy-uzeit,

lwa_time like line of lr_time.

lwa_time-Low value of range will have UZEIT-15 mins.

lwa_time-HIGH will have UZEIT

lwa_time-SIGN = I

lwa_time-Option = BT.

append lwa_time to lr_time

select *

from <your table name>

into table it_final

where status = 'XYZ'

  and zzsubmitted_at in lr_time.

[venkat_aileni]

Hi-

This is what I would do:

Create a new table which holds a single entry for maintaining my batch run details. Like

Batch_job_name, Last run date and time.

In my program I will write a query on this table to fetch last run batch details and accordingly I will retrieve details from the table you have specified. Last step in my program is to update this entry with current date and time.

If you don't want to create a table I think you can look for ABAP memory concept, like EXPORT and IMPORT the internal which holds this batch job details.

Hope this helps!

-Venkat

[Former Member]

Hi,

You can extract the previous job run details from TBCO table and compre with your table to extract the quotes. 

Thanks,

Kiran.

[venkateswaran_k]

Dear Koushik,

I have similar requirement but to select the records created for past 15 minutes..  Study this program and apply this logic.

This program select all material documents created for past 15 minutes.(thrugh mb1b)  and send a notification by email

Use the function to calculate time -

'SWI_DURATION_DETERMINE'

"Notification for MB1B
select * from mkpf into CORRESPONDING FIELDS OF TABLE  it_mkpf where cpudt = sy-datum and tcode2 = 'MB1B'.
loop at it_mkpf into wa_mkpf.
  t_time = wa_mkpf-cputm - 900.

    CALL FUNCTION 'SWI_DURATION_DETERMINE'
    EXPORTING
      start_date = sy-datum
      end_date = sy-datum
      start_time = wa_mkpf-cputm
      end_time =  sy-uzeit
    IMPORTING
      duration = lv_seconds.

    duration = lv_seconds / 60.
    if duration <= 5.
*    write:  /0 wa_mkpf-mblnr, 30 wa_mkpf-cpudt,  50 wa_mkpf-cputm,  70 duration.

      CALL FUNCTION 'ZMM_NOTIFICATION_MB1B'
      EXPORTING
      AUDAT         = sy-datum
      LMBLNR         = wa_mkpf-mblnr.  "ti_resb_new-rsnum.
    endif.

endloop.

Regards,

Venkat

Let me know if this is helpful to you

[Former Member]

Hello,

I need to Query the Table directly in the time frame of this 15 min. So i need the select Query for this.

For Example :

Now the Time is 11.30 AM. I need to get the entries from 11.15 AM to 11.30 AM .

There is one field which holds this Submitted time in the table.

BR,

Kaushik

[nabheetscn]

Try as suggested above

[venkateswaran_k]

Dear Kaushik,

I dont think you cannot query directly as you dynamicaly to calculate the last 15 minutes.

As I shown in the program,

You select all records updated today and put it in loop.

loop

calcuate the time gap using the function (i mentioned) with the system time.

if it is <= 15 min,

   take action

endif

endloop.