Learn and share on deeper, cross technology development topics such as integration and connectivity, automation, cloud extensibility, developing at scale, and security.
I want to share with you one of the best program from my toolbox. I called it ZTOAD, in reference of a famous query builder in the SQL world.
As you know, SAP don't give to developper tools to execute query (there is a crappy tool for admin, and some function modules for devs, but not so usable...).
So i took my keeboard and made my own...
With ZTOAD, you can write and execute queries, in Open SQL format (the format used in ABAP program). Result is displayed in an ALV at bottom part of the screen. You can access to DDIC to help you when writing query and use SAP standard help.
You could save your best queries, and also share it to friends.
With this tool it become very easy to debug a complex query in production (with lot of join or subqueries for example). Just copy past the query in the editor and run, you will see in live the result.
And there is a direct download link (remember that you will need SAPLINK with table extension to install) :
Final bonus: if ZSPRO is also installed, ZTOAD will get tables listed in ZSPRO repository and display it in a tree under DDIC tree (to help you to create queries)
For the next release of your tool I have the following Feature proposal: Code Completion :smile:
To implement it, just add the following lines of Source Code to FORM init_editor on line 1092 of ZTOAD program:
" " Activate Code Completion and Quickinfo " o_textedit->init_completer( ). DATA(lo_completer) = o_textedit->get_completer( ). SET HANDLER lo_completer->handle_completion_request FOR o_textedit. SET HANDLER lo_completer->handle_insertion_request FOR o_textedit. SET HANDLER lo_completer->handle_quickinfo_request FOR o_textedit.
I have been using se16 & se16n for a long time out of convenience when putting solutions together - but this is handy for ad-hoc query or putting something together that will be more complex.
I would really like to see the native SQL conversion like you can with PeopleSoft SQL editor, in addition to the generated source code.
If there is one very slight negative is that SE16/n tool offers ability to look at possible entries with drop down fields on a selection screen upfront which at times is most useful.
Brilliant stuff! Very much like what I would want & expect from an SQL editor.
One question though: when using the context menu to delete all items in the queries tree on the left side, it really deletes all entries disrespecting the current folder I've focused on.
For example I would like to delete history items only, I focus on the tree leaf "history" and select "Delete all" from the context menu. Instead of just deleting the sub-entries from "History" it too deletes my saved queries.
Could you please fix this in one of you next releases?
- Fix issue with delete all history context menu - Add Code completion on SQL editor (Thanks to Benjamin Krencker for his code) - Add Remove useless APPENDING TABLE statement in query
I've another one: when using a statement like ...AND KZWI3 <= '23,00' I get a short dump about CONVT_NO_NUMBER. Of course it's clear what caused this exception, however, could you please enclosed the appropriate (generated) coding inside a try...catch..endtry statement and show it as an e.g. error dialog or perhaps a status message?
Today I got a GENERATE_SUBPOOL_DIR_FULL short dump, see attached image. Don't know exactly what I did before but anyway I was extensively testing (many executions) an SQL-statement without leaving ZTOAD at any time.
After some try, it seem that the CONVT_NO_NUMBER error is not catchable in this context...
If you find a working solution i take it :wink:
The GENERATE_SUBPOOL_DIR_FULL dump is a standard process. SAP does not allow more than 36 generated subroutine. For ZTOAD it mean that you cannot execute more than 36 sql command during 1 run.
There are 3 solutions :
- i can count the sql and stop execution when 36 is reached
- i can rework a little to generate subroutine in an external memory area (with a submit)
- i can rework a lot to no more generate subroutine (with RTTS)
I think i will be limited in query design if i use RTTS. I will probably do the 2nd solution for a next release.
I've probably found a new issue with ZTOAD. I tried the following SQL statement, which runs fine inside a plain ABAP report but not within ZTOAD.
Sorry for posting an image <rant> but this damn SCN-editor does not allow pasting any code into this text box. I know this is a problem solely with IE but obviously SAP is not capable of fixing such simple things anytime soon </rant>
Anyway, when executing this statement, I get an error that the compiler could not interpret "4". Guess this is due to the above used CASE..WHEN '4'..END construct.
If I change the statement to WHEN status = 4, I get a new error: There is no component with name "CASE".
I'm running 7.40 SP8. Obviously ZTOAD has some problems with the new OpenSQL syntax I guess.
Thank you for your suggestion. In fact i've not planned to deliver translated version (as English become, year by year, more and more mandatory in IT...)
But, if you want you can translate it in german easilly by using the sap standard translation mechanism.
Then simply use saplink to create a new nugget, include program ZTOAD and table ZTOAD :smile:
ZSPRO is not mandatory, you can use ZTOAD without having ZSPRO (and without change any code in ztoad :wink: )
ZSPRO is another piece of my toolbox, not yet published here. It's a launcher/repository where you can centralize objects (program, tables, transaction, function module, IMG activity, class, url). For each object, you can execute, display documentation, search where it is used, display code/definition...
I was not able to find a license under which you release ZTOAD. Have you considered adding one yet? I'd really love to use it and give it a try, but the absence of a lincense leaves the terms of use unclear..
You are probably right, but i'm not lawyer, and very bored by all this long text i dont understand.
To be clear :
- You can install and use ZTOAD
- You can modify and rename ZTOAD. If you think your modifications can be usefull to others, feel free to share your modifications here
- You can sell ZTOAD to customers. I just ask you to not lie to your customer and say that you are the owner of this program.
- As the program does not contain any malicious code, you are responsible of all damages done by the program. Before use, you can check and review the source code to be sure :wink:
Great work, cheking the CL_ABAP_PASER problem I ended up adjusting a new version (3.5) wich i hope can be usefull and hope you revise to publish in your site.
The changes i implemented are:
Use of CL_ABAP_PASER depending on class implementation in the sistem.
Removed table ZTOAD, the program uses report variants instead of a Z table.
Cleared all dynpro objects: the program is developed for copy & paste without the need of dynpros (everithing works in the selection screen).
Thank you for sharing your code. Lot of things changed ! Very difficult to check the difference as you renamed all objects (variable ok, your naming rules, but why rename subroutine ???) and change order for some objects...
I do not create "copy/paste" program anymore since i've found SAPLINK :smile:
But when i did it, i used the same tricks you used (overwrite the selection-screen, use report variant table...). There are nice but stay dirty trick, less robust than true dynpro, true table, so i think it's better to keep the actual version regarding this changes.
For information, the function CONVERT_TABLE_TO_STRING you used doesn't exist on my sap system
I've scanned the code to find all other différences. I hope i've not missed one...
I will keep :
- New query button
- Move option button in main toolbar
- Adapt the default query to the limits defined for the user
- Change "exit without save" popup
- Refresh ddic tree when execute query, even if an error occur
- Remove confirmation popup on display code for no select statement
And merge theses changes to my local new version which contain some cool features !
I will also have a look on the CL_ABAP_PARSER "problem" but your solution reduce the number of allowed query by 1, so i'm not sure it's a good way in this case...
s_customize-auth_insert shoud be s_customize-auth_delete
WHEN 'DELETE'. SPLIT lw_query AT space INTO fw_table fw_param. TRANSLATE fw_table TO UPPER CASE. IF fw_table = 'FROM'. SPLIT fw_param AT space INTO fw_table fw_param. TRANSLATE fw_table TO UPPER CASE. ENDIF. CLEAR sy-subrc. IF s_customize-auth_object NE space. lw_table = fw_table. AUTHORITY-CHECK OBJECT s_customize-auth_object ID 'TABLE' FIELD lw_table ID 'ACTVT' FIELD s_customize-actvt_delete. ELSEIF s_customize-auth_insert NE '*' AND NOT fw_table CP s_customize-auth_insert. sy-subrc = 4. ENDIF.
Thanks for the new version, but when I try to install it SAPLINK abends:
Is my SAPLINK outdated or is it a corrupt ztoad nugget file?
PS installed the newest Version with the same result (the new version captured the exception if rootnode is initial, but same rootnode stays initial, Checked the nugg file and its is really a nugg file.
A responsible company will never have such a tool in productive server.
It maybe useful for development issues, in integration systems it may be helpful for testing under live conditions. In productive systems, you will run into legal trouble.
As you can manage authorization, you can prevent all "riskly" operations. So there is no good reason to forbid this kind of tool on all productive servers... for a limited population, with limited access, of course.
Or you have to delete also all admin transactions, as you can do some riskly operation with them !
SE14 can drop the content of any standard table, for example.
Thanks a lot! I've activated DDIC extension in system. But I got another dump when I import ZTOAD nugg. Maybe it is because that somebody else has imported it before but failed which generated some data inconsistancy in system table. Do you know how to solve it?
Hi, very good tool.
But i can give you the idea for the next step.
The next step is to make it undestand dynamic SQL, because in different ABAP versions there are different restrictions for dynamic SQL, for example, dynamic SQL cannot handle subqueries like
select * from table where db_key in ( select distinct foreigh_key from other_table where some_field in range ). inner join here will produce duplicates .
also i think that removing duplicates by using DISTINCT also not allowed in dynamic sql, because it doesn't recognize field distinct.
I will look at your code and think about it , maybe when i will have sufficient time .
BTW do you have this source code on github ? You may use something like ABAPGit, the source code will be easily readable on github.
Thanks for sharing your work this tool is very useful. But, when using many aggregations in the select statement (MIN, MAX, SUM), sometimes the program ends with a dump. This is how I solve it, just in case. Maybe you can consider it in another release.
The select statement is this:
SELECT e~ebeln e~ebelp e~bwart b~exnum
MIN( a~eindt )
SUM( e~menge )
MIN( b~bsart )
MIN( b~ekgrp )
MIN( b~waers )
MIN( b~wkurs )
MIN( b~lifnr )
MIN( b~inco2 )
MIN( b~ekorg )
MIN( d~name1 )
MIN( c~txz01 )
MIN( c~menge )
MIN( c~meins )
MIN( c~netwr )
SUM( e~wrbtr )
MIN( e~budat )
FROM ekbe AS e
INNER JOIN ekko AS b ON b~ebeln = e~ebeln
INNER JOIN ekpo AS c ON c~ebeln = e~ebeln
AND c~ebelp = e~ebelp
INNER JOIN lfa1 AS d ON d~lifnr = b~lifnr
INNER JOIN eket AS a ON a~ebeln = e~ebeln
AND a~ebelp = e~ebelp
WHERE e~ebeln = '4500000000'
AND e~ebelp = '00010'
AND e~zekkn = '' " Núm.Actual Imputación
AND e~vgabe = '1' " Histor.Pedido=Ingr.Merc.
AND e~bewtp = 'E' " Tip.Hist.Pedido = Entrega
AND e~bwart = '101'
AND c~bstyp EQ 'F'
AND c~loekz EQ space
AND c~pstyp EQ '0'
GROUP BY e~ebeln
e~ebelp
e~bwart
b~exnum.
The dump was the following:
To solve this, its necessary to clear the variable “LW_STRING2” before the DO-ENDDO instruction (in the subroutine GENERATE_SUBROUTINE). This variable keeps the name of the tablefield. So, if you have more than one field with an aggregation, the program will take the first tablefield read in the SELECT statement and will give a dump.