The screen below shows the body of the InSights Into Data query. Let's take a moment and go over the various parts, how to use them, and what effect they have on the query and the results.
Query Name: This field gives you the display name of the Query. The actual unique identifier is a GUID (32 bit character field) called QryID that is held in the ZQryRpt table - but never really shown in the user interface.
Category: This field is strictly for organizational purposes! IT has not affect on the Query or the Results.
Non-Owner: This field tells the program what privileges to give anyone but the person who created the Query. You can set it to:
- Edit - allows anyone to alter and edit the query.
- Use - other user cannot edit it - but they can run it.
- None - no one else besides the author can use or run the Query.
Query Notes: These are notes for the entire query (all Scripts). This is the best way of documenting why you created the Query.
Database Viewer: The far right sides shows the current database you are connected to. Here you can see any Tables and Views in the database. A single click on them will show the field list in the middle of the viewer, and a double click will also show the first 100 records of the table or view. This can be very handy when you are writing your SQL Scripts as it allows you to see both the fields and a sample of their actual data.
Query Script Body:
The big area in the middle is the Query Script Body. This area allows to add/edit multiple Query Scripts per Query that can either run an SQL script against the database or import a file from disk.
The above screen capture shows a Query Script body with two different Scripts. Let's go over the different elements and how they can be used.
Script Tab: The script tabs at the top part of the Query Script Body area allow you to create and edit multiple scripts in this one query. You can change the name of the tab by double clicking the tab itself. The name you choose for this tab - is what will be used for the name of the tab in the Results area.
Result Table Name: *IMP* this field is what you will be naming the inmemory datatable. This name is VERY Important - as it is what is used in the Reports, Dashboards, etc. If you build a Report or Dashboard and rerun the query but have a different name of the inmemory table - it will not work as expected.
Type: The type field allows you to determine what type of Query Script this entry is.
Query: This type will run the SQL script and put the result in the Grid - and then show it.
Analysis: This type will run the SQL script and put the results in the Pivot Grid and then show it.
Dashboard: This type will run the SQL script, populate the grid - but also display the dashboard
File Import: This type will run the File Import script and then populate the grid and then show it.
SQL Target: This field will tell the SQL engine whether to run the SQL script against the Data database or the Config Database.
Data Database: This tells the program to run the query against the standard Data Database.
Config Database: This tells the program to run the query against the Configuration database - which allows you to use InSights Into Data to analyze it own configuration tables
Script Body: This area is where the SQL script or File Import script is located. You can edit either directly here - but in reality only the SQL script is easily hand edited. The Import Script should be edited only by using the File Import Wizard.
Script Notes: This field allows you to record notes for each individual script (File Import or SQL). This is an important way to provide documentation for your scripts in InSights Into Data.
Buttons (Right Side)
Visual Query Builder: This button launches the Visual Query Builder that allows you to create and edit SQL scripts using drag and drop and fill in the blanks.
Large SQL Editor: This button will launch the Large SQL Editor. This provides a full screen window where you can edit larger SQL scripts. It can be handy if you have complex SQL statements or a small screen.
After Query Script: This launches theAfter Query Script editor. When launched from this button it allows you to edit the script and automatically save it to the Query you are editing. It is important to note that this script is run right after this Query Script completes. So - if you have multiple Query Scripts - and want the After Query Script to run after they have all run and completed the import to the datatables - then you need to put it on the last Query Script.
Import File: This button will launch the File Import Wizard. IT will take you through the screens to create the script for the Script editor. It does not run the import at time, but generates the script to be run at a later time - and saves it to the script body.