Skip to Content

How to run SAP query in background and create a file in application server

It is possible to run SAP queries in background and to create a query output as a file in application server.

Follow below steps to implement the same.

1) As per OSS note 537735 – SAP Query: save to file in the background activate the enhancement SQUE0001 using the t.code SMOD.

2) Once the above enhancement is activated, it will add a new radio button option with text “Private file” under the output format block of the Query selection screen.

2-4-2014 3-00-13 PM.jpg

3) Create a Z table and maintain the sever filepath in which the files to be stored in application server.

2-4-2014 3-27-48 PM.jpg

4) Go to the function exit “EXIT_RSAQEXCE_001” and do your customized coding in the INCLUDE ZXQUEU01.

*Data Declaration.

DATA : c_lv_buf TYPE string,

      c_lv_line TYPE string,

      c_lv_filepath TYPE localfile,

      c_lv_query TYPE aqs_quname.

*Field symbols

FIELD-SYMBOLS : <fs_record> TYPE ANY,

                <fs_comp> TYPE ANY.

*Get a Query name from the program

CALL FUNCTION ‘RSAQ_DECODE_REPORT_NAME’

  EXPORTING

    reportname    = program

  IMPORTING

    query          = c_lv_query

  EXCEPTIONS

    no_query_report = 1

    OTHERS        = 2.

IF sy-subrc <> 0.

  MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

          WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

*Select the filepath from ZBSD_T0246 table based

*on the query name and variant.

SELECT SINGLE filepath

  FROM zbsd_t0246

  INTO c_lv_filepath

  WHERE query = c_lv_query

  AND qvariant = syst-slset.

IF sy-subrc = 0.

*Open application server file.

  OPEN DATASET c_lv_filepath FOR OUTPUT IN TEXT MODE ENCODING DEFAULT.

  IF sy-subrc = 0.

    CLEAR c_lv_line.

*Create header of the file

    LOOP AT listdesc.

      CONCATENATE c_lv_line listdesc-fcol ‘;’ INTO c_lv_line.

    ENDLOOP.

    TRANSFER c_lv_line TO c_lv_filepath.

    CLEAR c_lv_line.

*Transfer the data to the file

    LOOP AT datatab ASSIGNING <fs_record>.

      DO.

        ASSIGN COMPONENT sy-index OF STRUCTURE <fs_record> TO <fs_comp>.

        IF sy-subrc <> 0.

          EXIT.

        ENDIF.

        c_lv_buf = <fs_comp>.

        IF sy-index = 1.

          c_lv_line = c_lv_buf.

        ELSE.

          CONCATENATE c_lv_line c_lv_buf INTO c_lv_line SEPARATED BY ‘;’.

        ENDIF.

        CLEAR c_lv_buf.

      ENDDO.

      TRANSFER c_lv_line TO c_lv_filepath.

      CLEAR c_lv_line.

    ENDLOOP.

*Close the file once the datas are transfered.

    CLOSE DATASET c_lv_filepath.

    IF sy-subrc = 0.

*File created in path &1

      MESSAGE s483(zbsd_0001) WITH c_lv_filepath.

    ENDIF.

  ENDIF.

ELSE.

*File path not maintained in table ZBSD_T0246

  MESSAGE e484(zbsd_0001).

ENDIF.

5) Run the query in the background and the files will be created in the application server path maintained in Z-table.

25 Comments
You must be Logged on to comment or reply to a post.
  • Hi Thanga,

    Nice Blog…..very useful for a bigger ….could have added some more images and a screen shot of how the file would look…needs a bit more explanation and details in some sections.

    Cheers

    Piyas

    • Hello Swadhin,

      Thanks. It depends on the coding which you do inside the enhancement.

      I have done it in such a way that it will store files in application server.

      Might be you can do a coding to save files in the presentation server by using GUI_DOWNLOAD. instead of OPEN DATASET, TRANSFER and CLOSE DATASET.

      Regards,

      Thanga

  • hi Thanga,

    very helpful, works fine and saved me time I could use for other stuff 🙂

    e.g. I kicked the Z-table and now I fetch the path and filename directly from the selection screen.

    2014-06-02 13_35_10-MLE(4)_100 SAP Query.png

    the code snippet:


    * fetch the path and file from the Query-Parameter %PATH

         lv_global_field = ‘(‘ && program && ‘)%PATH’.

         assign (lv_global_field) to <pathname>.

         if sysubrc  0 and <pathname> is assigned.

           lv_filepath = <pathname>.

         endif.


    remark: do not use the variable %PATHNAME as it is empty while running in background!


    br

    christian

    • Hi Christian,

      I was thinking of the same idea. But the code snippet is not working. It seems not to possible to pick the value from Screen Field %PATH with the snippet.

      Did it really work for you?

      Thanks for your feedback.

      • *hehe* tricky.

        started the query directly in SQ01 and found that the assign does not fetch the %PATH 😯

        then I started via program SAP_QUERY_CALL (the one to use when executing a query via job) and everything fine:

        2015-03-31 16_09_03-MLE(4)_100 ABAP Debugger(5)  (exklusiv)(atvies9912vx_MLE_23).png

        and yes, the customer drives the thing in a daily manner.

        br

        christian

        • Hi Christian,

          thanks a lot for your quick response. I’m going to try it in backround later.

          But if I use the code as you’re using it I get a syntax error. Strange.

          The <pathname> I’ve declared as a field-symol and TYPE ANY.

          The was declared like this: lv_global_field TYPE localfile.

          Do you have a guess on that?

          Unbenannt.JPG

  • Dear Thanga,

    At first , thanks a lot for post. I tried to run query , but always getting the error e484(zbsd_0001). All tasks done as you described. Please help.

    Shahin

  • Hi, unofortunately I’ve some trouble to make it run smoothly.

    I got a error calling the FM RSAQ_DECODE_REPORT_NAME .

    It says the Parameter Query is not defined. I’ll try to set the parameters manually and check if it works.

    Update: I found the reason. The IMPORTING statement was commeted.

    CALL FUNCTION ‘RSAQ_DECODE_REPORT_NAME’
       EXPORTING
         REPORTNAME            = program
      IMPORTING   <———-
    *   WORKSPACE             =
    *   USERGROUP             =
         QUERY                 = c_lv_query
    *   CLIENT                =
      EXCEPTIONS
        NO_QUERY_REPORT       = 1
        OTHERS                = 2

  •  

    Hello Thanga,

     

    Really Nice post.. but we have a different problem…

     

    Although we have followed the steps but it is not showing the ‘Private File’ Option at all. I even tried placing a debugger inside the Exit but it is not hitting there …

     

    Any suggestions

     

    Regards

    Manish Agrawal