Sometimes i need to write sql statements and get results immediately in abap. To handle this requirement, i create a test program, write my sql in it and debug to investigate results.  It’s difficult and time consuming work so I have developed an SQL tool for ABAP Programmers to allow them to run select statements directly in SAP, confirm or discover relationships and see data side by side.

 

 

Installation:

 

1- Create an executable program  and named it “ZSQL” in se38

2- Download attached zSQL.txt.zip file and Upload into zsql(or copy&paste)
(Source Code: https://blogs.sap.com/2013/10/20/zsql-a-tool-to-execute-sql-statements-directly-in-sap-source-code/ )

3- Create screen ‘0100’

4- Go into screen ‘0100’, choose “Flow Logic” tab and add flow logic code as below

5- Go to layout of screen and add a “Custom Control” and name it “CC_SQL”

6- In layout add two buttons

     -Run: FctCode: “RUN”

     -Exit: FctCode: “EXIT”

7- Activate screen

8- Activate program

 

 

Flow Logic Code:

PROCESS BEFORE OUTPUT.

   MODULE status_0100.

 

PROCESS AFTER INPUT.

   MODULE user_command_0100.

 

 

Notice:

     – Each FROM/JOIN clause must be in new line

     – Table names&Aliases and From/Join clauses must be in the same line

     – Don’t use SELECT SINGLE

     – Don’t use INTO statement

     – Doesn’t support aggregate functions(e.g. SUM, MAX, AVG)

     – DELETE/UPDATE statements are not supported in default, but you can allowed in test systems by uncommenting lines in subroutine “check_operation”

 

 

 

Examples:

select1.JPG

out1.JPG

*

select2.JPG

out2.JPG

*

update.JPG

*

del.JPG

 

 

P.s. Eitan Rosenberg did some modifications on the screen. I didn’t change my original code because of the easy installation cares, And also putting screen shot to give you an idea how it can be modified for better user experience. Here is the power of SCN 🙂 Thanks Eitan.

/wp-content/uploads/2013/10/uploadedimage_294937.png

To report this post you need to login first.

62 Comments

You must be Logged on to comment or reply to a post.

  1. Nguyen Van Thao

    Thanks for the useful tool, Basar 🙂 . You can also write SQL statement directly if your database is Oracle with tCode ORA_PERF –> Additional Functions –> SQL Command Editor.

    (0) 
  2. Eitan Rosenberg

    Hi,

    Thank for sharing.

    I did not have the chance to try it yet but I am going to purpose it for our consultants as a tool to be used when writing technical specifications.

    Some of them are not from IT background so they do not know SQL very well. so it is a learning tool as well.

    Regards.

    (0) 
    1. Basar Ozgur Kahraman Post author

      Hi Eitan,

      it is very good idea to use it for newbies. I remember my early times that began to coding in Abap, i hardly need this kind of tool in 4.6c 🙂

      Regards

      Basar Ozgur

      (0) 
      1. Bipin Poddar

        Hi Mr. Basar ,

        I’m new  in Abap and  I’ve created that Zsql program but editor doesn’t come.

        Are there any changes in the code ?

        It shows  “Buttons” only and <Exit > button works very well.

        Kindly , tell me where am I wrong ………?

        With best regards

        (0) 
  3. Eitan Rosenberg

    Hi again.

    I did some small modification to make it more appealing for the uninitiated…

    Unfortunately there is no facility (Why ?) to send a picture here.

    Regards.

    Eitan.

    (0) 
      1. Denise Landers

        Hi Basar,

        Thank for the tool source code.

        I have implemented the steps you described above.

        When I execute the sql statement “select * from ADR6”, I get the following error:

        “The statement INTO CORRESPONDING is not expected.  A correct similar statement is MOVE-CORRESPONDING…”.

        In your code I see where you are appending the statement “INTO CORRESPONDING FIELDS OF TABLE p_it_return”.

        I have attempted to modify that line to use MOVE-CORRESPONDING, but have not been successful.

        Do you have an update for the code?

        Thank you,

        Denise

        (0) 
          1. Denise Landers

            Hi Matthew,

            Yes, I stepped through the debugger to see that it was adding the SQL “INTO CORRESPONDING…”.

            The error suggested that it needs to be changed to “MOVE-CORRESPONDING…”.

            I cannot use MOVE-CORRESPONDING in openSQL.

            Denise

            (0) 
        1. Basar Ozgur Kahraman Post author

          Hi Denise,

          First of all, you can not use MOVE-CORRESPONDING instead of INTO CORRESPONDING in openSQL statements. Have you try Matthew’s suggestion? What is the result? i am using tool without any problem. i have started to follow you so if your problem still continue, you can reach me via direct message.

          (0) 
    1. Bipin Poddar

      I’m new  in Abap and   I’ve created that Zsql program but editor doesn’t come.  Are there any changes in the code ?  It shows  “Buttons” only and <Exit > button works very well.

      Will u plz. tell me where I’m wrong ………???

      With best regards

      (0) 
    1. Basar Ozgur Kahraman Post author

      Hi Volker,

      Please, read notices more carefully. in notices section there is an info that says that “DELETE/UPDATE statements are supported only in test systems“.

      Have you installed program? when you install, you will see there is subroutine that name is “check_operation”, you can edit it easily to deny delete/update functions.

      i should put this functions enable in test systems default, because this is a sql tool.

      Regards

      Basar Ozgur

      (0) 
    2. Matthew Billingham

      Indeed. I wouldn’t let this program out of a development system. Certainly it would allow a malicious person to wreak much havoc – but that can be done in dev anyway.

      I wouldn’t allow it in a test system, as updating data there will likely screw up the utility of the test data.

      Even in read only mode it cannot be permitted in a productive system as the person running it will be able to see data in all transparent tables.

      (0) 
      1. Basar Ozgur Kahraman Post author

        Hi Matthew,

        This is a tool for developers. i usually using it in unit testing and to compare the performance of join statements to decide the best one. As Eitan Rosenberg said it can also be used as a learning tool.

        (0) 
  4. Matthew Billingham

    I strongly suggest that this program, if it is to be installed at all, only be installed in your development system, and that the update/delete functions be entirely removed.

    (0) 
    1. Basar Ozgur Kahraman Post author

      Hi Matthew,

      i have just updated code & blog notices. Now delete/update operations not allowed in default. But i didn’t remove whole code only comment it. So still there is an option to execute these commands under developer control.

      Thank you for your advice

      Best Regards

      Basar Ozgur

      (0) 
        1. Eitan Rosenberg

          Hi,

          It is very easy to write a program to do some damage and to transfer it to production system some time we EVEN do it and then we call it a bug…..

          Eventually every thing is depend on the integrity and ethics of people.

          And this is nothing to do with SAP…..

          Regards.

          (0) 
          1. Matthew Billingham

            There is a difference between introducing a bug and deliberately putting a risky/destructive program into production. Most companies have procedures in place to prevent deliberately destructive programs getting beyond test. (It’s one of the reasons why source code review is vital). It is much easier for a developer to deliberately take out development than to take out a test/productive system.

            The program should not be be allowed in production, as the user would be able to look at any data – violating any data security process.

            I can see its usefulness in a test environment – but I would not permit update/delete, as it would be too easy to end up with a inconsistent database and a ruined test environment.

            In development all bets are off, so I see no reason for it not to exist there.

            (0) 
    1. Bipin Poddar

      Hi

      I’m new  in Abap and   I’ve created that Zsql program but editor doesn’t come.  Are there any changes in the code ?  It shows  “Buttons” only and <Exit > button works very well.

      Will u plz. tell me where I’m wrong ………???

      With best regards

      (0) 
  5. Bipin Poddar

    Hi Dear,

    I’m new  in Abap and   I’ve created that Zsql program but editor doesn’t come.  Are there any changes in the code ? only , It shows  “Buttons” only and <Exit > button works very well.

    Pls., help me….?

    With best regards,

    (0) 
    1. Basar Ozgur Kahraman Post author

      Hi Bipin,

      You need to create a custom control with name “CC_SQL“. Please apply installation steps again carefully. if you still have problems, you can connect me via private message.

      Best Regards

      (0) 
  6. Umit Coskun Aydinoglu

    It will be very helpful if it is possible to add update/insert/delete statements into customizing request for customizing table for cases like copying a template.

    (0) 
    1. Basar Ozgur Kahraman Post author

      Hi Umit,

      Update and delete statements aren’t supported by default because of the sensitive responses of community 🙂 They are optional and you can activate them by uncommenting related lines.

      Regards

      (0) 
  7. César Augusto Scheck

    Hi Basar, thanks for your initiative!

    If I perform some fix or add some new feature on your code, I’ll update you in order to share it along the community.

    By now it’s working well!

    Thanks again,

    César Scheck

    (0) 
  8. Miguel Enriquez

    Hi, thanks for sharing:

    1) i downloaded the zSQL.txt.zip.

    2) create a new zsql program (executabel) on the transaction se38.

    3) opened the file zSQL.txt and paste in the editor.

    4) followed the instructions…

    -Create screen ‘0100’

    -Go into screen ‘0100’, choose “Flow Logic” tab and add flow logic code as below

    Go to layout of screen and add a “Custom Control” and name it “CC_SQL”

    In layout add two buttons

         -Run: FctCode: “RUN”

         -Exit: FctCode: “EXIT”

    Activate screen

    Activate program

    in this last step, i get a error:

    Program ZSQL

    Teh exception CX_SALV_MSG is neither caught nor is it declared in the

    RAISING clause “RUN”

    the error is here:



    CALL METHOD cl_salv_table=>factory

    IMPORTING

           r_salv_table = _cl_salv

    CHANGING

           t_table      = <_it_dyntable>.

       _cl_func = _cl_salv->get_functions( ).

       _cl_func->set_all( ).

       _cl_salv->display( ).

    any advice why?

    (0) 
    1. Basar Ozgur Kahraman Post author

      Hi Miguel,

      i think your ECC version is different.

      Could you please surround “call method cl_salv_table=>factory” section with try/catch block and check the message again.

      For ex.

      DATA: lv_msg TYPE REF TO cx_salv_msg,

                   lv_string TYPE string.          

      TRY.

           CALL METHOD cl_salv_table=>factory

           IMPORTING

                  r_salv_table = _cl_salv

           CHANGING

                  t_table      = <_it_dyntable>.

      CATCH cx_salv_msg INTO lv_msg .

           lv_string = lv_msg->get_text( ).

           message lv_string type ‘I’.

      ENDTRY.

      (0) 
      1. Miguel Enriquez

        Hi System-> Status tell me i have SAP ECC 6.0,

        Changed the code, same as you recommendation and worked…

        but i try test:

        Select * up to 10 rows from lfa1

        and

        Selct * up to 10 rows from lfa1

        (yes with error) in both cases, click on rub button and NOT happend anything.

        any advice wich is wrong?

        (0) 
        1. Basar Ozgur Kahraman Post author

          Hi Miguel,

          please read notices section of blog more carefully.

               – Each FROM/JOIN clause must be in new line

               – Table names&Aliases and From/Join clauses must be in the same line

               – Don’t use SELECT SINGLE

               – Don’t use INTO statement

               – Doesn’t support aggregate functions(e.g. SUM, MAX, AVG)


          Please write from/join clauses in new lines and don’t use aggregate functions.


          Regards

          (0) 
          1. Miguel Enriquez

            hi i try:

            Select *

            from GLT0

            up to 10 rows

            where rtcur = ‘MXN5’ and drcrk = ‘H’

            and

            Select *

            from GLT0

            up to 10 rows

            where drcrk = ‘H’

            click on run button and not happening anything or show the result 🙁

            wich is wrong?

            thanks

            (0) 
            1. Basar Ozgur Kahraman Post author

              Hi Miguel,

              Below statement working for me. Can you debug program?? i started to follow you, so you can send me results via direct message. After solving error, you can share reason and solution here..

              Regards

              Untitled.jpg

              (0) 
  9. Miguel Enriquez

    Hi Friends, return to the BEST tool of SQL, a question.

    if the program not work on AGREGGATE functions (i am interested n SUM) how do get the max date of bkpf?

    of course i try:

    Select max ( budat )

    from bkpf

    of course not work… i know but i try.

    but too try:

    Select *

    from bkpf

    up to 10 rows

    order by budat desc

    the error is: ‘Unknown column name “DESC” field list. field list. field list.’

    and not work 🙁

    Any advice?

    Thanks

    (0) 
  10. Roberto Vacca

    Hi.

     

    If you put a ‘*’ asterisk or comment ” in the first line, you can always update and delete..

    a check against comments bypass can be useful.

    Anyway always  a good tool 🙂

     

    (1) 

Leave a Reply