Skip to Content
Author's profile photo Sébastien HERMANN

ZTOAD – Open SQL editor

Hi everybody,

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.

http://quelquepart.biz/data/images/ztoad/ztoad.png

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.

French presentation can be found here : http://quelquepart.biz/article7/ztoad-requeteur-open-sql

And there is a direct download link (remember that you will need SAPLINK with table extension to install) : http://quelquepart.biz/telechargements&file=L2RhdGEvZG9jdW1lbnRzL3p0b2FkLnppcCozZThiNjE,&source=scn-ZTOAD

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)

Feel free to comment here 🙂


My others blogpost :

ZAL11 : a replacement for AL11

LISTCUBE replacement : a new way to display data

Abstraction class to generate MSWORD with SAP using OLE

Generate DOCX file in ABAP

ZAUTODOC : Automatic generation of technical BW documentation

Assigned tags

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

      Thanks for sharing your work.

      Very interesting, like always!

      Two small bugs I've run into:

      1. When ZSPRO table doesn't exist, the search functionality (in right pane) doesn't work (FM POPUP_GET_VALUES fails).

      I've edited method handle_header_click_ddic (lines 469-470) and replaced field ZSPRO-TEXT with RSDXX-FINDSTR.

      2. When F1 is pressed and no text is selected a dump occurs.

      I've added "lw_cursor_pos_to <> lw_cursor_pos_from" check

      in method handle_f1_textedit (line 379).

      One additional feature/improvement I may suggest:

      1. It would be nicer if double-clicking on table field in the right pane will add it automatically to the SQL/source code instead of just exporting it to clipboard like now.

      Drag and drop support might also be useful.

      I've already implemented it in my system.

      You may review the modified code here.

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      Very nice. I'm pleased to read you 😀

      Thanks for the 2 bug fix and for the feature "ready to paste"

      I included them. Just have to do some more tests and i will release new version with theses fix/enh

      I made some minor changes from your implementation (for example drop si done at the cursor drop position, instead of last cursor position)

      Author's profile photo Former Member
      Former Member

      Wow... I'm impressed. I managed to create this kind of program similar to SQL's "Query Analyzer" but without such a sophisticated interface called Z_QUERY_ANALYZER. Just simple text editor to write Open SQL and ALV Table to show the result.

      Image 1.png

      Image 2.png

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      The first version of ZTOAD was similar to your screenshot 🙂

      Author's profile photo Former Member
      Former Member

      Merci Sébastien.

      Quite a fantastic tool. 🙂

      Author's profile photo Former Member
      Former Member

      Amazing job .. very useful. there was another tool ZSQL I was using earlier developed by another amazing programmer. Now I will use this 🙂

      thanks.

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      Thanks 🙂

      As i said on my blog, ZTOAD is born on the ashes of ancestors (Z_TETARD & YES4SQL). I see with your comments that there was a lot of ancestors... a lot of ashes 😀

      Author's profile photo Basar Ozgur Kahraman
      Basar Ozgur Kahraman

      Hello Raghavendra & Sebastien,

      First of all thanks a lot to mention about my tool zSQL: A tool to execute SQL statements directly in sap

      And congratulations to Sebastien, another wonderful tool for developers. ZTOAD seems so cool, it deserves five stars. Thank you for your effort.

      Best Regards

      Author's profile photo Rainer Hübenthal
      Rainer Hübenthal

      thanks for that great tool, disabling the form routine where zspro is refered solved the problem

      Author's profile photo Custodio de Oliveira
      Custodio de Oliveira

      Very nice stuff, Sebastien, thanks for sharing!

      I too have seen similar custom-built tools, but yours is the nicest I can remember.

      Would you consider uploading it to github (or anything similar) so it would be easier for the community to report bugs, suggest changes, etc?

      Cheers,

      Custodio

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      Thank you 🙂

      I'm not very familiar with github, but as i understood, it's not really usable for abap. You cannot use directly your local fork of the project, need to upload into sap, do the changes and then download from sap to share the changes...

      i think it's finaly simplier to use this blog or mine to share enhancement 😉

      Author's profile photo Lars Hvam
      Lars Hvam

      consider using Git client for ABAP - alpha release it will do the uploading and downloading directly from SAP, if your system is connected to the internet

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      "alpha release" ? 😀

      I think i will continue to use SAPLINK for a while, it is a robust and "validated" tool 🙂

      But maybe in few years...

      Author's profile photo Former Member
      Former Member

      Your tool looks great!

      Is there a software license for the tool?

      Thanks,

      Thomas

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      you could use it, enhance it, share it freely.

      Just 2 requirements :

      - if you enhance it, share me 🙂

      - If you want to make money with it, then tell me before in MP 😉

      Author's profile photo Former Member
      Former Member

      ok great,

      could you please officially attach an open source license?

      GPLv2 for example would be appropriate according to your requirements.

      For legal requirements we cannot install any software without a license... 😉

      Author's profile photo Marian Bauersachs
      Marian Bauersachs

      Bonjour Sébastien HERMANN
      il y a une nouvelle bonne page d'aperçu où tous les projets ABAP Open Source sont listés.
      J'ai également téléchargé votre version de ZTOAD dans un dépôt.
      https://github.com/marianfoo/ztoad
      Il est nécessaire de spécifier une licence. Je vous ai indiqué dans le README et j'ai sélectionné la licence "GPL-3.0 License".
      Cela vous convient-il ?

      Salue
      Marian

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      Hello Marian,

      Merci pour l'info. N'hésitez pas a me remonter les éventuelles modifications ultérieures.

      Pour la licence, je n'ai pas eu le temps de me pencher dessus, je ne pourrais donc confirmer si c'est la bonne.

      Désolé :-/

       

      Author's profile photo Justin Loranger
      Justin Loranger

      Sebastien,

      I have not yet looked at the code.  But one question, is there any risk of harmful scripts being used with it, i.e. DELETE FROM VBAK, etc?

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      at the very begin of the code, you can define globally the pattern of allowed table for each SQL command (select, insert, update, delete).

      You could also use a specific authorization object to restrict range of table allowed by user 🙂

      IE :

      1 user can have access to select on any table, insert/update on Z*, delete on ZTEST*

      1 other can have access to select only on Z* table, and no access to insert/update/delete

      etc.

      Author's profile photo Clemens Li
      Clemens Li

      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.

      Best regards, Clemens

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      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.

      Author's profile photo Former Member
      Former Member

      This is pretty much like Hovitaga OpenSQL Editor... http://www.hovitaga.com

      The UI looks very similar. Congrats!

      Author's profile photo Former Member
      Former Member

      Actually, There are some similarities in the UI (Well, SAP UI options are pretty limited), but (at least for now) the Hovitaga solution is much more robust.

      They seems to do a pretty good job and included many nice features.

      The most interesting feature, in my opinion, is the queries pipelining ("Select for all entries in" syntax) option.

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      Hovitaga is very complete (wizzard, "for all entries" management...) and not free 🙁

      But you are right, the look & feel is similar 🙂

      For everyone who want to buy Hovitaga, you could test mine before and if it correspond to your requirement, give me only half of the price 😈

      Author's profile photo Former Member
      Former Member

      Wonder if it would work fine with the craziest queries from this blog 😀

      A complete guide to OpenSQL statements - Step-by-step tutorial with screenshots

      Nice tool, I'll check it later today!

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      A user give me a crazy query that make ZTOAD mad...

      I released a new version to cure my son 🙂

      Changelog :

      - Add Manage drag&amp;drop from DDIC tree to SQL Editor

      - Mod Double clic on field in DDIC tree paste field in editor instead of filling clipboard

      Thanks to Shai Sinai for this 2 points

      - Fix management of table alias

      - Fix Issue with comment removing

      - Add Ignore "into table" statement

      - Add Ignore alias in field list

      Author's profile photo Former Member
      Former Member

      Dear Sébastien

      I have run the nugget for same but when I am activating  Report 'ZTOAD' , I am getting Error as ' Type "ZSPRO" is unknown '  pls let me know how to resolve this error.

      regards

      Gautam Malhotra

        
      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      Hello,

      You just have to remove or comment all the subroutine where append the error. 🙂

      Author's profile photo Former Member
      Former Member

      Hi,

      One more comment regarding the authorization object:

      How about replacing the 4 separate ID fields with standard TABLE, ACTVT fields?

      i.e.

      AUTHORITY-CHECK OBJECT s_auth-auth_object
      ID 'TABLE' FIELD lw_table
      ID 'ACTVT' FIELD actvt_auth_display.

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      You are right, it's easier for developper point of view :

      - easier to implement the auth object

      The problem is ACTVT has a restrictive value list.

      Which actvt choose for each allowed SQL statements ?

      For the moment, there is no problem, the more accurate may be

      * 01 - Create for INSERT

      * 02 - Change for UPDATE

      * 03 - Display for SELECT

      * 06 - Delete for DELETE

      Theses activity may be explicit for the 4 sql statements.

      But the current design has been built to be flexible. In a future release i can add statements like create/delete index. How i will manage them in ACTVT ?

      Author's profile photo Former Member
      Former Member

      Well,

      I agree that ACTVT isn't flexible, but I would prefer to stick with it.

      I guess that In case of new supported statements, I would add additional field for object type: Table, Index, etc.  (maybe DICOBJTYP).

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      Understood, you could easily replace the current authority check to use ACTVT / TABLE 🙂

      Actually, auth object have all theses fields independants.

      Your proposal fit more with sap standard auth strategy where all fields in an auth object are linked.

      I can implement one or other in ztoad, your proposal is better because fit sap standard way.

      I will probably add in the next release a "NATIVE" command to execute any NATIVE SQL command. In actual design, it will be a 5th independant parameter, no problem.

      But it cause a problem with your proposal :

      you will have 2 dependants fields (ACTVT & TABLE) and 1 field independant (NATIVE).

      It's a pain to maintain auth with this kind of object.

      The proper way is to use 2 auth objects : 1 for each set of dependants fields

      Maybe you have a better idea ?

      Author's profile photo Former Member
      Former Member

      Hi Sébastien,

      You can take a look at the row and column level authorizations of Hovitaga OpenSQL Editor here:

      Security overview

      I think you cannot go further than this.

      Bye

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      Thank you for the link 🙂

      Hovitaga go very far in auth level for "classical" query. The probable target is to replace SQ01 and build reports.

      ZTOAD go in another direction. This is an IT tool. As a tool, it must be the most usable as possible. I love having a lot of blades on my knife, even if each blade is less polished than an other knife 😉

      So ZTOAD will not go down to line or column level on authorization. If you need this kind of thing, use (and pay) Hovitaga, i think i cannot do better 😉

      But, for hovitaga team : we can ALWAYS go further. Why not add a time auth on line. For example : you can modify data only for 1 to 5 of the month, or you can modify data that is not older than XXX days... It make sense for some business ^^

      Author's profile photo Former Member
      Former Member

      There can always be more complex options which will require some smart adaptation (or even introduction of separate authorization object, as you've suggested) 🙂

      Regarding your actual new option (Native SQL),

      I'm not sure if you are planning to parse the SQL statement or just execute it.

      For the second I would probably just add auth. check of DUMMY table and ACTVT 16 (Execute).

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      initialy, i think to parse one by one the allowed native command. (1 auth field for each command).

      But... i'm afraid to forgot one very important command and be blocked in an emergency production problem, so i think i will package all native command in 1 auth field "NATIVE"

      16 - Execute : is not really evident... but maybe could be acceptable.

      Ok ok i could include the change in the next release...

      Somebody think that its a bad idea ? have another idea ?

      Author's profile photo Former Member
      Former Member

      Hi,

      Great Job,

      After running NUGG file, below dump i am getting, please let me know how to solve it.

      ZTOAD_DUMP.PNG

      Thanks in advance.

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      Hello,

      Have you installed the "TABLE" extension for saplink ? this is a mandatory prerequise 🙂

      https://www.assembla.com/spaces/saplink-plugins/subversion/source/HEAD/branches/DDic/0.1.0/NUGG_SAPLINK_DDIC.nugg

      Author's profile photo Former Member
      Former Member

      Hi,

      I'm getting dump while importing nugget file.

      Capture.JPG

      Thanks

      KH

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      I tried to do the install on a fresh sap system :

      First point is that the lattest saplink version is bugged on my older sap system. I cannot activate it.

      Then, I used an older one, posted on 2014-10-10,

      Install SAPLINK from SAPLINK_INSTALLER OK

      Activation of SAPLINK OK

      Install of DDIC extension OK.

      Activation of DDIC Extension OK

      Download of ZTOAD.zip OK

      Unzip of ZTOAD.zip OK

      Install of ztoad.nugg using ZSAPLINK OK

      Activate table ZTOAD in SE11 OK (with warning)

      Activate program KO : It was a too old sap version : i removed manually some too new syntax and then activation OK.

      Maybe you missed ddic extension table Katrice ? Or your saplink is corrupted.

      I will work to enhance ZTOAD compatibility with older sap system (but it's activation errors, not installation errors).

      Author's profile photo Former Member
      Former Member

      Hi,

      Following is my system config.

      Capture.JPG

      Thanks

      KH

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      ok you run on a verry recent sap version.

      But which saplink version are you using ?

      Try another saplink version, older or newer... that's all i can tell you...

      Author's profile photo Former Member
      Former Member

      Hi,

      How to check the saplink version?

      Thanks

      KH

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      As they remove version info in lattest release... i dont know 🙁

      If you run saplink. Does the title display version number or just "saplink" ?

      Author's profile photo Former Member
      Former Member

      Hi,

      It just shows as below

      Capture.JPG

      Thanks

      KH

      Author's profile photo Former Member
      Former Member

      Hi,

      Following are the program attributes.

      Capture.JPG

      Thanks

      KH

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      OK, no version info...

      Have you installed DDIC extension ?

      https://www.assembla.com/spaces/saplink-plugins/subversion/source/HEAD/branches/DDic/0.1.0/NUGG_SAPLINK_DDIC.nugg

      If no, it is mandatory and may be the cause of your dump.

      If it is already installed... you could try this SAPLINK version :

      http://saplink.googlecode.com/files/SAPlink_install-0.1.4.zip

      Dont forget to install DDIC extension for saplink after installing this version.

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      Hello,

      There is a new 3.2.1 version which take all your feedbacks in account :

      - Manage new SQL Syntax introduced by NW7.40 SP5

      - Add All NATIVE Sql commands

      - Auth object use now the sap standard way

      - Automatic removing of useless INTO TABLE statement in query

      - Default limit to 100 rows is now optional

      and some bugfixs :

      - Fix Dump in case of up to xx rows in unioned query

      - Fix Dump at activation if ZSPRO does not exist

      - Fix Compatibility issues with older sap system

      Author's profile photo Former Member
      Former Member

      Hi,

      Thanks for the ongoing updates.

      I've replaced the default internal "UP TO X ROWS" statement with a new button in our system:

      /wp-content/uploads/2015/09/ztoad_max_rows_787151.jpg I may share the code if you're interested.

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      you have lot of nice ideas 🙂

      Which kind of element did you used to add this line ?

      I understoud you splitted the screen, but not sure which kind of object you inserted... HTML ?

      Author's profile photo Former Member
      Former Member

      Actually, it's a small trick:

      It's a simple standard toolbar with a standard button.

      When you press the button an input popup is displayed.

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      OK please, send me the code, it could be usefull to add some other buttons 🙂

      Author's profile photo Former Member
      Former Member

      Your'e welcome.

      Here is the source code.

      It includes also some other modifications.

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      There is a new version 3.3 :

      - Add New Options panel to save user preferences
      - Add Delete all history entries context menu
      - Add option to add linebreak after paste field from ddic tree
      - Add Count column in alv grid display

      Thanks again to Shai Sinai for his suggestions

      Author's profile photo Benjamin Krencker
      Benjamin Krencker

      Hi Sébastien

      For the next release of your tool I have the following Feature proposal: Code Completion 🙂

      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.

        o_textedit->register_event_completion( ).
        o_textedit->register_event_quick_info( ).
        o_textedit->register_event_insert_pattern( ).

      Afterwards, Code Completion shows up after pressing [CTRL]+[SPACE]

      Code_Completion.PNG

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      Wonderfull ! i didnt understood how to add it 😳

      Done on the dev version, will be available in next release 🙂

      Author's profile photo Sven Küter
      Sven Küter

      Good job

      Author's profile photo Former Member
      Former Member

      great tool (again) Sébastien !

      will make good use of this.

      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.

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      Thank you Steve 🙂

      Nice idea, i will see if i can do something for you 😉

      Author's profile photo Michael Fritz
      Michael Fritz

      Sébastien,

      thanks for this wonderful tool!

      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?

      Thanks,

      Michael

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      Hello,

      Sorry about that ! Its now fixed.

      Changelog :

      - 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

      Thank you for the alert 🙂

      Author's profile photo Michael Fritz
      Michael Fritz

      Thanks!

      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.

      Michael/wp-content/uploads/2015/11/2015_11_03_130454_823628.png

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      Hello,

      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 😉

      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.

      Author's profile photo Silvio Miranda
      Silvio Miranda

      Vey good tool.. congratulations!!!

      Author's profile photo Michael Fritz
      Michael Fritz

      Sébastien,

      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>

      /wp-content/uploads/2015/11/2015_11_23_103030_835843.png

      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.

      Michael

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      You are right. Sap introduce lot of new syntax  in 7.40 and again in 7.50.

      Theses new syntax elements are not managed by ZTOAD.

      The only new syntax elements managed are :

      - comma between fields in select part

      - alias for fields

      - @ for variables

      I do no say that it is not possible to manage them. But i will no do it mainly because i have no access to a 7.40/7.50 sap system to test changes.

      Author's profile photo Former Member
      Former Member

      Michael,

      It's your chance to enrich the tool and share the code 🙂 .

      Author's profile photo Former Member
      Former Member

      very good job Sébastien.

      Cool that there is a open source solution. I'm using http://www.cadaxo.com/content/en/products

      since several years, for all my customers. It's really the best SQL tool I've every seen.

      This tool support also the new SQL possibilities from SAP.

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      Thank you Markus 🙂

      The tool you link is not free, like hovitaga. If you find customers that want to pay for this kind of tool, it's wonderfull !

      All my tools are free and open source, and it will never change.

      I have already  received commercial offer for some of them, but i'm not interrested 😉

      Author's profile photo Olivier SEGOL
      Olivier SEGOL

      Hello Sébastien,

      Very usefull. Thank you very much for sharing it.

      Olivier

      Author's profile photo Dominik Di Lorenzo
      Dominik Di Lorenzo

      Hello Sébastien,

      Thank you very much for sharing this great tool.

      Since working with SAP for the first time, I have searched for exactly this program 🙂

      If I may help you with translating it to German, please send me a direct message in SCN and tell me how I could help you best.

      Best regards,

      Dominik

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      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 🙂

      Author's profile photo Dominik Di Lorenzo
      Dominik Di Lorenzo

      Hello Sébastien,

      I published a version partly translated to german here:

      ZTOAD 3.4.3 - partly translated to German

      I tried to also get the dynpro and status texts to the nugget, but I did not know how to do it.

      Best regards,

      Dominik

      Author's profile photo Sigurður Njarðvík
      Sigurður Njarðvík

      Hi Sébastien

      very nice sql editor, but one question, where can i find ZSPRO


      regards

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      Hello,

      ZSPRO is not mandatory, you can use ZTOAD without having ZSPRO (and without change any code in ztoad 😉 )

      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...

      If you are interested, you can find it on my blog

      http://quelquepart.biz/article5/zspro-parametrage-specifique

      The synergy with ZTOAD is that ZTOAD add in the top/right part of the screen (in the "sap table" tree) all tables defined in ZSPRO.

      So if you build a repository of used tables in zspro, you can retrieve it in ztoad to help you to write quick query.

      Author's profile photo Former Member
      Former Member

      Hi,

      thanks for the post - and the tool!

      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..

      Regards,

      Gero

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      Hello,

      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 😉

      Author's profile photo Raymond Giuseppi
      Raymond Giuseppi
      Author's profile photo Wallace Ferreira e Silva
      Wallace Ferreira e Silva

      I try to instal ztoad but does not exist CL_ABAP_PASER in my system. I just coment the code refer by lo_completion.

      It´s work fine. Great job !

      Thank you !

      Author's profile photo Patrick Prime Reinoso
      Patrick Prime Reinoso

      Hello Sébastien,

      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).
      • Other minor tweaks.

      The nugget with the source:

      https://drive.google.com/file/d/0BzsIL0aR8IgiOWw0OGN3Z1lETzQ/view?pref=2&pli=1

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      Hi Patrick,

      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 🙂

      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...

      New version will be available here soon 😉

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      Hello,

      There is a new version of ZTOAD. Thanks to Patrick Prime Reinoso for many of theses changes.

      Here is the changes :

      * Add Value Help on DDIC field. Select a value to paste in editor
      * Add Button Execute into file to download results instead of display it in ALV

      * Use class CL_RSAWB_SPLITTER_FOR_TOOLBAR for creation of DDIC toolbar

      * Refresh the DDIC tree when executing a query even if error found

      * Remove confirmation popup on display code for no select statement

      * Add Option to display technical name in ALV

      * Move option button to main toolbar

      * Rename subroutines (code cleaning)

      * Allow save on exit popup

      * Add New query button

      * New query template changed

      Author's profile photo Silvio Miranda
      Silvio Miranda

      Very good work.. I installed and tested... the code is very clear...

      Author's profile photo Sigurður Njarðvík
      Sigurður Njarðvík

      Hi  Sébastien

      I found small bus in your latest version 3.5

      on lines 3259-60

      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.


      And on lines 1660-61

      The query

      select

      From ztable

      causes runtime error CX_SY_RANGE_OUT_OF_BOUNDS


         lw_offset = ls_find_select-offset + 7.
         lw_length = ls_find_from-offset - ls_find_select-offset - 7.


      is fixed with


         lw_offset = ls_find_select-offset + 6.
         lw_length = ls_find_from-offset - ls_find_select-offset - 6.

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      Thank you for the report 🙂

      Both points are now fixed in 4.5.1.

      For the 2nd issue, i prefered check the lw_length value as i want to remove the space after "SELECT", so the +7 offset is correct.

      Author's profile photo Rainer Hübenthal
      Rainer Hübenthal

      Thanks for the new version, but when I try to install it SAPLINK abends:

      /wp-content/uploads/2016/07/ztoad_990635.png

      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.

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      Could you please retry ?

      I think it's an Unicode problem... I changed the format of the nugg into the ZIP, it may be ok now.

      Author's profile photo Rainer Hübenthal
      Rainer Hübenthal

      Thx, but now I cant unzip/extract the nugget:

      /wp-content/uploads/2016/07/winzip_991298.png

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      OK, new package again, sorry

      Could you please retry ?

      Author's profile photo Rainer Hübenthal
      Rainer Hübenthal

      TN-5144-super.gif

      Author's profile photo Rainer Hübenthal
      Rainer Hübenthal

      By the way, your ZAL11 Nugget has the same issue. Notepad++ identifies the encoding as UC2 BOM, saving it as UTF-8 is doing the trick,

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      You are right. It's linked to a new tool i tried to implement... Fixed now for ZAL11, i have to check all other files...

      Author's profile photo Long Zheng
      Long Zheng

      When I import ZTOAD nugget in ZSAPLINK. It shows:

      PROG ZTOAD

      Plugin for object type TABL is not installed on this system

      I can confirm I've installed DDIC extention. What's the reason?

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      Maybe you dont have activated the DDIC extension before import ztoad ?

      Author's profile photo Long Zheng
      Long Zheng

      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?

      /wp-content/uploads/2016/08/2016_08_24_18_32_10_1022053.png

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      possible, you could try to delete ZTOAD program and ZTOAD table in your system and restart import.

      Maybe it is saplink that is broken. You could try to remove all saplink program and class, and recreate them, reactivate them and reimport the nugg.

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      About new syntax managment.

      I plan to add "case" management this week end. Any other usefull command that you wish to see in ZTOAD ? (please give a query example 🙂 )

      Author's profile photo Sabrina Villa
      Sabrina Villa

      Hey Sebástien,

      Down in the comments I posted a small bug I found in the program, maybe you could consider that for another release. Thanks again for this tool 🙂

      Regards,

      Sabrina Villa

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member

      Also using ranges can be helpful too.

      For example

      data: lr_date type range of table-created_at,

      ls_date like line of lr_date.

      ls_date-sign = 'I'. ls_date-option = 'BT' . ls_date-low = '20170101'. ls_date-high = '20170424'.

      append ls_date to lr_date.

      select * from table where created_at in lr_date.

      Author's profile photo Tomas Holy
      Tomas Holy

      Hi  Sébastien,

      I am trying to get the source of your tools, but unfortunatelly the given linkes doesn't work anymore. Is there any chance to still get them?

      THX Tomas

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      Sorry, my website was down partially last summer. It's fixed, you could retry 🙂

       

      Author's profile photo Sabrina Villa
      Sabrina Villa

      Hello Sébastien,

      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.

       

      Greetings,

      Sabrina Villa

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      Hello Sabrina,

      Thanks a lot for your feedback and your detailed solution.

      I will post a new version ASAP 🙂

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      New version released with your fix, thanks again Sabrina 🙂

       

      Author's profile photo Former Member
      Former Member

      Hi,

      I always get this error:

      The type "ZTOAD" is unknown.

      How can I fix it?

       

      Thanks a lot

      Author's profile photo Tuba Ipek
      Tuba Ipek

      Your ZTOAD DDIC table is inactive.

      Author's profile photo Hrishikesh Katti
      Hrishikesh Katti

      Thanks a lot Sebastien for this wonderful tool.

      Is there any possibility to add 'Number of Entries' feature like we have in SE16? The count shown in the ALV output at the last row is very useful but if the data is in millions it will be difficult to load the entire Data in ALV Output which might cause dump. If it is possible to add this feature, it will be useful in case of joins of multiple tables also.. 

      Once again thanks a lot for this excellent and useful tool..

      Regards,

      Hrishikesh.

      Author's profile photo Shai Sinai
      Shai Sinai

      I think you may just use a SELECT COUNT(*) statement.

       

      Author's profile photo Sigurður Njarðvík
      Sigurður Njarðvík

      It looks like ZSAPLINK can no longer install the .NUGG package

      It fails with the following runtime error

      An exception has occurred which is explained in more detail below. The
      exception is assigned to class 'ZCX_SAPLINK' and was not caught in procedure
      "CREATE_PFSTATUS" "(METHOD)", nor was it propagated by a RAISING clause.

      Currently there is no fix implemented for this program from http://saplink.org

      possible solution here
      https://blogs.sap.com/2016/03/22/fix-saplink-regarding-message-ec473-sap-note-2159455/

      so the solution may be to migrate over to use ABAPgit  https://github.com/larshp/abapGit

      Regards Sigurdur

       

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      Hello,

      From which SAP version have you got this dump ? I have never encountered this issue.

       

      For the ABAPGit solution, it could be possible, only if it can be used 100% without any internet access.

       

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      I could switch to abapgit only if following requirement is meet :

      – no need internet connexion (not always possible to have it)

      – no need separate package for each application (not always possible to have creation autorization for a package…)

      – possible to simply import/export objects, like with saplink (and possibility to select which objects !)

       

      I quickly tested abapgit, and it seem that it does not meet theses requirements.:(

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      Hi Sigurdur,

       

      I checked deeper in the saplink code / fix.

      Are you sure you are using the latest saplink version ?

       

      Latest version can be found here :

      https://github.com/sapmentors/SAPlink/tree/master/build

      SAPlink_Daily.nugg

      This version should solve your dump as it have partial implementation of your link which fit 100% with the ZTOAD program.

       

      Regards,

      Sebastien

       

      Author's profile photo Kellen Yan
      Kellen Yan

      Hello Sébastien,

      I encountered the following error when importing ZTOAD using ZSAPLINK.

      Am I missing any key components?

      Thanks a lot.

      Kellen

      Author's profile photo Shai Sinai
      Shai Sinai

      Hi,

      It seems you are missing the SAPLink plugin for TABL (Tables):

      https://github.com/sapmentors/SAPlink/wiki/SAPlink-plugin-list

      Author's profile photo Kellen Yan
      Kellen Yan

      Hello Sébastien,

      Can we not limit it? Like ZSQLEXPLORER.

      Thanks a lot.

      Kellen

      Author's profile photo Sébastien HERMANN
      Sébastien HERMANN
      Blog Post Author

      Hello,

       

      I'm not sure that it's a good idea, but if you want to store more than the 100 last queries, simply modify the lw_number definitionin the repo_save_current_query form.

      line 2994 :

      lw_number(2)     TYPE n,

      To set the limit to 1000, change to : lw_number(3) type n,

      limit 10 000 ? change to : lw_number(4) type n,

      You can try "no limit" with a type string : lw_number type string (not sure that you will not have to change a little line 3051 / 3054 in this case)

      Author's profile photo Kellen Yan
      Kellen Yan

      Ok,let me try.

      Thanks a lot.