Skip to Content
Author's profile photo Basar Ozgur Kahraman

zSQL: A tool to execute SQL statements directly in sap

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

Assigned Tags

      63 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member

      DB6PERF - DB6: DB2 UDB Cockpit Performance

      ORA_PERF - DBACockpit: Oracle - Performance

      I think these transactions are not quite the right tool for running SQL commands...

      Author's profile photo Eitan Rosenberg
      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.

      Author's profile photo Basar Ozgur Kahraman
      Basar Ozgur Kahraman
      Blog 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

      Author's profile photo Bipin Poddar
      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

      Author's profile photo Mohammad Anees Hayat
      Mohammad Anees Hayat

      please add Custom Control on Screen layout  with the naem "CC_SQL".

       

      Author's profile photo Former Member
      Former Member

      Simple and Nice article Thanks...

      Author's profile photo Eitan Rosenberg
      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.

      Author's profile photo abhijeet kankani
      abhijeet kankani

      Why dont you Upload File!!!

      Author's profile photo Eitan Rosenberg
      Eitan Rosenberg

      Hi,

      Unfortunately there is no facility to that as well ?

      Do you have those option when you added your comment ?

      Regards.

      Author's profile photo Basar Ozgur Kahraman
      Basar Ozgur Kahraman
      Blog Post Author

      Hi Eitan,

      Did you download source code attached to blog? if you have problems, don't hesitate to inform me. I will help you

      Regards

      Basar Ozgur

      Author's profile photo Eitan Rosenberg
      Eitan Rosenberg

      Hi,

      Works like a charm !!!!

      Regards.

      Can you follow me so I can send you a picture.

      Author's profile photo Denise Landers
      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

      Author's profile photo Matthew Billingham
      Matthew Billingham

      Have you tried debugging and seeing if you can figure out where it is going wrong and how to fix it?

      Author's profile photo Denise Landers
      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

      Author's profile photo Basar Ozgur Kahraman
      Basar Ozgur Kahraman
      Blog Post Author

      Hi Denise,

      Please read notice section of blog carefully. you must write from/join clauses in new lines.

      "Each FROM/JOIN clause must be in new line"

      Regards

      Author's profile photo Basar Ozgur Kahraman
      Basar Ozgur Kahraman
      Blog 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.

      Author's profile photo abhijeet kankani
      abhijeet kankani

      Thank for sharing i have created it IDE

      Best,

      Abhijeet kankani

      Author's profile photo Bipin Poddar
      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

      Author's profile photo Basar Ozgur Kahraman
      Basar Ozgur Kahraman
      Blog Post Author

      Hi Bipin,

      You have to complete installation steps carefully, especially step 5 for your problem..

      regards

      Author's profile photo chandan mishra
      chandan mishra

      Nice Document....

      Author's profile photo Mehmet Ozgur Unal
      Mehmet Ozgur Unal

      Fast solution for rounding life 🙂

      Thanks for sharing...

      Author's profile photo Former Member
      Former Member

      Hi Basar,

      Its wonderfull, many thanks 🙂

      Author's profile photo Former Member
      Former Member

      Nice tool! 😀

      Author's profile photo Former Member
      Former Member

      Hi Basar, very cool tool! 😎

      Author's profile photo Former Member
      Former Member

      nice tool Basar,

      Keep sharing such information.

      cheers 🙂 ,

      PS

      Author's profile photo Former Member
      Former Member

      ...and yet another way to destroy the system without leaving any traces. Let me see, DELETE FROM mara...

      Author's profile photo Basar Ozgur Kahraman
      Basar Ozgur Kahraman
      Blog 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

      Author's profile photo Former Member
      Former Member

      Basar Ozgur,

      it is excellent to see that only development and test systems can be destroyed without leaving a trace.

        Volker

      Author's profile photo Basar Ozgur Kahraman
      Basar Ozgur Kahraman
      Blog Post Author

      Hi Volker,

      i updated my code, Update/delete operations not allowed in default. Now you can try it in secure

      Thanks for your comments

      Regards

      Basar Ozgur

      Author's profile photo Matthew Billingham
      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.

      Author's profile photo Basar Ozgur Kahraman
      Basar Ozgur Kahraman
      Blog 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.

      Author's profile photo Former Member
      Former Member

      Hi Basar Ozgur Kahraman

          

           Excellent piece of programming. Thank you for sharing. Keep it up.

      Regards,

      Rashmith

      Author's profile photo Former Member
      Former Member

      HI Basar Ozgur Kahraman,

      very useful tool, thanks For sharing with detail steps.

      Regards,
      SG

      Author's profile photo rajesh bethamcharla
      rajesh bethamcharla

      Hi Basar Ozgur Kahraman

      Good one... Thanks for sharing.... 🙂

      Regards,

      Rajesh

      Author's profile photo Matthew Billingham
      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.

      Author's profile photo Basar Ozgur Kahraman
      Basar Ozgur Kahraman
      Blog 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

      Author's profile photo Matthew Billingham
      Matthew Billingham

      Well, there's nothing to prevent a developer from writing a program to delete MARA. It's outside of dev systems that you need more care.

      Author's profile photo Eitan Rosenberg
      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.

      Author's profile photo Matthew Billingham
      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.

      Author's profile photo VIJAYKRISHNA GUDALA
      VIJAYKRISHNA GUDALA

      Hi Basar Ozgur Kahraman Useful one. Thanks and keep going. 🙂

      Author's profile photo Bipin Poddar
      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

      Author's profile photo Bipin Poddar
      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,

      Author's profile photo Basar Ozgur Kahraman
      Basar Ozgur Kahraman
      Blog 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

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Basar Ozgur Kahraman
      Basar Ozgur Kahraman
      Blog 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

      Author's profile photo César Augusto Scheck
      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

      Author's profile photo Buddhika Krishantha
      Buddhika Krishantha

      Thanks for sharing.

      It's really helps.

      Author's profile photo Former Member
      Former Member

      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?

      Author's profile photo Basar Ozgur Kahraman
      Basar Ozgur Kahraman
      Blog 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.

      Author's profile photo Former Member
      Former Member

      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?

      Author's profile photo Basar Ozgur Kahraman
      Basar Ozgur Kahraman
      Blog 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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Basar Ozgur Kahraman
      Basar Ozgur Kahraman
      Blog 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

      Author's profile photo Former Member
      Former Member

      Done!!!, Done!!!!

      i forget assign the Function code for the button RUN.

      Thanks.

      great....

      Author's profile photo Praveer Sen
      Praveer Sen

      Nice Artical..thanks for Sharing.

      Happy Learning... 🙂

      Reagrds.

      Praveer.

      Author's profile photo Former Member
      Former Member

      Nice tool. Regards!

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Basar Ozgur Kahraman
      Basar Ozgur Kahraman
      Blog Post Author

      Hi Miguel,

      Could you please try like below;

      SELECT *

      FROM bkpf

      UP TO 10 ROWS

      ORDER BY budat DESCENDING


      Regards,

      Author's profile photo Former Member
      Former Member

      i am happy

      Done....

      Thanks.

      Basar Ozgur Kahraman Thanks.....

      PD: in the future you add Agreggate Functions?

      Thanks

      Author's profile photo ECS SAP Team
      ECS SAP Team

      Hi Basar,

      Can you please tell us where is the attached zsql zip file. I am not able to see it in your post.

      Thanks

      Author's profile photo Basar Ozgur Kahraman
      Basar Ozgur Kahraman
      Blog Post Author

      Hello,
      I guess link was removed during scn migration. I added link to installation steps number 2.
      Regards

      Author's profile photo Roberto Vacca
      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 🙂