zSQL: A tool to execute SQL statements directly in sap

[basarozgur_kahraman]

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:






*





*



*



 

 

P.s. rosenberg.eitan 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 :smile: Thanks Eitan.

[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?

[fcorodriguezl]

Nice tool. Regards!

[basarozgur_kahraman]

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

[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 :sad:

wich is wrong?

thanks

[basarozgur_kahraman]

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

[Former Member]

Done!!!, Done!!!!

i forget assign the Function code for the button RUN.

Thanks.

great....

[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 :sad:

Any advice?

Thanks

[basarozgur_kahraman]

Hi Miguel,

Could you please try like below;

SELECT *

FROM bkpf

UP TO 10 ROWS

ORDER BY budat DESCENDING

Regards,

[Former Member]

i am happy

Done....

Thanks.

basarozgur.kahraman Thanks.....

PD: in the future you add Agreggate Functions?

Thanks

[former_member209955]

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

[basarozgur_kahraman]

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

[roberto_vacca2]

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 🙂

 

[former_member456950]

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