Aik Leemreize
Read all my blogsOne of the things I do in SAP, is making data queries… a lot of queries. I make them to answer a business question, to test if code works, or simply because I need to retrieve a certain record.
Most of the time transaction SE16 works fine for me. I export the results from one table and upload them again in the other tables selection screen. However, if the amount of data or total number of tables to join increases, the number of manual steps increases.
Since I want to limit manual work as much as I can, I therefore use the SAP QuickViewer (transaction SQVI). This tool allows you to make a join and quick representation of data in minutes, but is also very, very legacy and does not include certain functions, like advanced joining or sorting upfront.
Luckily there is an alternative: using the SQL Console in Eclipse…
With this tool you can easily create and execute your own openSQL-queries from outside the SAP-environment (eg. by not using the SapGUI). The SQL-console has been widely used for selecting data for quite some time but since SAP has its own development environment I was used to developing with the ABAP-tools like the workbench (SE80). Therefore the SQL-console was a gem I never used.
Steps to make it happen
- Install Eclipse (I use Mars, but Neon will work too)
- Download and install from https://www.eclipse.org/downloads/
- Add the SAP Development tools in Eclipse
- Go to Window -> Preferences -> Install / Update (left side) -> Available Software Sites -> Add https://tools.hana.ondemand.com/mars
- Go to Help -> Install New software -> Work With (select the site added above) -> Select ABAP
- Development Tools for SAP Netweaver (I just select all since I like software)
- Create an ABAP project – necessary to make a connection to SAP (Netweaver)
- Go to New -> Project -> filter on ABAP -> ABAP project
- Choose the system to connect to (these are the entries in your saplogon.ini)
- Next -> All details are taken from your saplogon.ini
- Fill in your username / password -> click finish
Now you should have a connection in your development environment that looks like this:
This connection can be used to directly do ABAP-development, but also to start your SQL console.
Results
Rightclick on the connection and click on SQL Console. Two panes open on the right side of the connection, showing an input field for your SQL-query and an output field for the results:
Fill in your query and press ‘Run’!
In the example I used, you can see in the subscreen below the query, that the SQL-query I used actually produced generated code in SAP. The time it took the system to execute the query is also shown. By right clicking your project and selecting the option ‘Open in Project’ you can make a comparison between the query on you current project (eg. development system) and another system (eg. production). This way, you can see how your query performs per system.
Be aware
To execute a query in the SQL-console, your user needs to have the authorization object S_ADT_RES on the system you execute it.
(Luckily) only SELECT statements are allowed. This way, no dangerous inserts or updates can be executed.
The limitation of 5.000 results in earlier versions has been upscaled to 100.000 results. This may impact performance when retrieving your data
Receive our weekly blog by email?
Subscribe here:
4 responses to “SQL Console for SAP Table selection”
You look like a BI consultant already… 😉
Don't we all prefer a little business intelligence…?
Very nice post, I looked for similar solution for years.
I had some issues with connection to https://tools.hana.ondemand.com/neon, due to the security cert issue. I found how to install company's certs to Java and after that all was fine.
Again, thank you very much for this post.
One of the other options that uyou can also consider, and which allows you to directly connect to Excel, various text formats, access and SQLServer; is the Winshuttle Studio Query component.