topic Re: Batch Program - Need to write Select Query in Application Development and Automation Discussions

Batch Program - Need to write Select Query

Former Member — Tue, 29 Oct 2013 04:14:42 GMT

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

Re: Batch Program - Need to write Select Query

FredericGirod — Tue, 29 Oct 2013 04:32:32 GMT

Hi,

give us the structure of your table

regards

Fred

Re: Batch Program - Need to write Select Query

venkat_aileni — Tue, 29 Oct 2013 04:43:29 GMT

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

Re: Batch Program - Need to write Select Query

Former Member — Tue, 29 Oct 2013 05:08:36 GMT

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

Re: Batch Program - Need to write Select Query

venkat_aileni — Tue, 29 Oct 2013 05:28:06 GMT

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

Re: Batch Program - Need to write Select Query

Former Member — Tue, 29 Oct 2013 05:34:03 GMT

Hello Venkat,

Can you paste the select Query for completing this answer.

Thanks alot,

BR,

Kaushik

Re: Batch Program - Need to write Select Query

Former Member — Tue, 29 Oct 2013 05:36:09 GMT

Hi,

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

Thanks,

Kiran.

Re: Batch Program - Need to write Select Query

venkat_aileni — Tue, 29 Oct 2013 05:42:19 GMT

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

Re: Batch Program - Need to write Select Query

venkateswaran_k — Tue, 29 Oct 2013 05:47:49 GMT

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

Re: Batch Program - Need to write Select Query

nabheetscn — Tue, 29 Oct 2013 05:54:02 GMT

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.

Re: Batch Program - Need to write Select Query

Former Member — Tue, 29 Oct 2013 05:58:51 GMT

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

Re: Batch Program - Need to write Select Query

nabheetscn — Tue, 29 Oct 2013 06:03:41 GMT

Try as suggested above

Re: Batch Program - Need to write Select Query

venkateswaran_k — Tue, 29 Oct 2013 06:11:47 GMT

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.