Q: Executing a query in Sui and presenting the result I MS-Excel is really cool and easy, how does it work?

A: Sui uses an open software component called HFFS (developed by the Apache Software foundation) to create a native XLS file. For more details about this component check out:

HSSF. For more details on how to use this feature in Sui check out: XLS Files in Sui.

 

Q: When I right-click in the query window I see a number of different way to execute a query, one of them Exec-MS Access is greyed out and I cant use it. What is this and why can’t I use it?

A: To export the result of a query to Microsoft Access you must have an open-Software component called Jackcess available on your machine. This component can be downloaded from www://jackcess/sourcefourge.net. After downloading it you have to put in your classpath (either in the system class path or adding it to the classpath from within Sui, Sui->preferences and the C-path tab). You must restart Sui after adding this component to the classpath.

When you are exporting data to Access, Sui creates a Access Database called SuiSys.mdb in the data path directory (specified under Sui->preferences Misc tab), the tables are called thhmmss (where hh are hours, mm are minutes and ss are seconds). Note that there are a number of limitations in the current implementation, some data types may not be set correctly in MS-Access.

 

Q: How are queries executed under Sui?(Applies to normal execution of queries)

A: Sui will send all queries for background processing. When the query is completed the query report window will be displayed. Sui fully supports parallel query execution. If you send a long running query for execution you can continue to work in Sui while the long running query executes. If you submit a short running query while a long running query executes you will probably get the result from the short running query before the long running query.

 

Q: How large result set can a query return in Sui? (Applies to normal quries)

A: This depends on a number of factors. The total size is limited by the memory available on the machine. A good approach is to set maximum Number of Rows to a value less then 1000. If you know that your query will return a large number of rows try to limit the number and the length of the columns in your result set. If your result set is to large Sui will display a Java out of memory message. (See next question for another approach to running queries with large resultsets).

 

Q: I want to execute a query that returns a large result-set. Is there a better and faster approach to run the query then running it as normal query (a query where the result-set is presented in a report window).

A: The most efficient way to run a query that returns a large result-set is to execute the query and write the result directly to a CSV file. To achieve this right-click in the query window and select EXEC->CSV.

 

Q: I am running a stored procedure that return multiple result-sets, does Sui support multiple result-set from a query or stored procedure?

A: Yes, Sui currently supports a single query returning up to 20 result sets. Each result set will create its own query report window.

 

Q: I want to run multiple SQL statements in the same query. Can I do that in Sui?

A: Yes you can, each SQL statement is terminated by a special character. You can assign the termination character from the preference panel, the default character is; Each SQL statement (that returns a result set) creates its own report window.

 

Q: Can I execute only a subset (for example the sub-select in a query) of an SQL statement?

A: Yes you can, make sure that you have checked the “Execute only highlighted SQL” check box under edit->preferences. If you highlight any part of the SQL Sui will only execute this part. If nothing is highlighted Sui will execute the entire SQL. The same technique can be used to format a subset of a SQL statement.

 

Q: I want to create a query against a table but I can’t remember the column names of the table, can I retrieve the column name for a specific table in Sui?

A: The easiest way to retrieve column names when you are writing a query is probably to enter the fully qualified table name, highlight it (mark it) and then select draw query to clipboard (either by selecting Options->Draw Query, selecting Draw query from the menu bar or pressing F11). When you have done this you have a complete select query in your clipboard just paste into the query window. (Mark table name, F11, Ctrl-v). If you want to create an insert query use the same technique but select to draw an insert query rather than a select query (F12).

You can also retrieve the column names by; “list table” and then “list columns”.

 

Q: I want to rearrange the columns on my report, how can I achieve that?

A: Select the column header drag it and drop it to where you want to move it.

 

Q: How do I access the column functions, and what column functions are available?

A: To access the column functions right click the column header (on some Operating systems this may differ, you should select the default pop-up trigger button). The following column functions are currently available:

• Hide Column, Hides the selected column
• Undo Hide column, brings back the last hidden column, the column will be placed to the left of the currently selected column
• Undo Hide of all columns, brings back all hidden columns
• Apply Filter, Applying a row filter for the selected column
• Ascending Sort, sort the selected column in ascending order. Null values are treated as low value.
• Descending Sort, sort the selected column in descending order. Null values are treated as low value.
• Column Information, Column information displays the following information:

oColumn Name

• Column Data Type
• Column Length
• Number of Rows
• Non Null rows, Number of non null rows (not displayed for fields with a length larger then 200 bytes)
• Lowest Value, Displays the lowest non null value (not displayed for fields with a length larger then 200 bytes)
• Highest Value, Displays the highest non null value (not displayed for fields with a length larger then 200 bytes)
• Sum, The sum of all non null columns, this is only available for columns with a numeric data type
• Average, The average value for all non null rows (SUM of all non NULL rows divided by number of non NULL rows), this is only available for columns with a numeric data type

 

Q: What does the list row function do?

A: The List row function is available from the report window (either by double clicking on a row, selecting a row and Options->List row or by selecting list row from the menu bar). It opens a new query report window and lists all columns for the selected row as rows. This can be seen as a detail view of a specific row in the result-set. This is very useful for reports with a large number of columns. You can use F7 to retrieve the previous row in the original result set and F8 to retrieve the next row.

 

Q: When I execute a query I get a message that says: “Actual Connection differs from Selected Connection, change URL or connect to selected connection”, what does this mean?

A: This error occurs when a user have changed the URL (In the URL combo box) without actually connecting to the selected URL. In this case the Actual Connection (displayed in the title bar) differs from the connection selected in the URL Box). To solve the problem you can change the URL to reflect the connection in the URL Box, or connect to the selected URL (Connect to database). You can also check the “Enable fast connection” check-box in Sui-preferences. This option changes the connection to the selected URL without reconnecting.

 

Q: I have started to execute a query that never seems to finish. Can I cancel the execution of this query?

A: There are two ways to cancel a query in Sui. The easiest way is to click the cancel all executing queries icon on the toolbar. This will kill all currently executing queries. If you have stacked multiple queries for execution and only want to cancel one query, go into the query monitor and select the query you want to kill.

 

Q: OK I have followed the example described in the previous query and killed a query, but the query will not end, it continues to execute. What is wrong?

A: SUI uses JDBC to execute SQL. JDBC supports canceling queries. Regretfully all underlying DBMS do not support this through JDBC.

 

Q: I want to print a report but the width of the report of is too large to fit into one page, what can I do?

A: First of all Sui will print the entire width of the report; if it don’t fit into one page it will print it on multiple pages. The first and most important thing to understand is that Sui prints the report exactly as you see it on the screen. If you want to try to fit into one page here are a couple of things you can do:

1. Change orientation from portrait to landscape (Select page Set-up)
2. Remove columns that you is not necessary on your report (Column functions->Hide column)
3. Resize the column width for some of the columns (Select the border of the column and drag it)
4. Reduce the font size of the report (select decrease font from the toolbar)
5. Reduce the default margins (Page set-up)

To verify if the report fits into one page or not, use print preview.

Q: How do I use the Format SQLnction?

A: Format SQL an ideal tool for complex SQL statements. It will indent your SQL query to make it more readable. It’s recommended that you use a non-proportional font to get the best effect on the formatting (use Edit-> Sui preferences and change the query font to monospaced).

 

Q: What is row filtering and how do I use it?

A: Row filtering lets you limit the number of rows on a query report. The filtering is achieved by applying one or more column filters. To add a column filter right-click on the column header and select Apply filter from the column menu and then add the filter. Filtering supports the following operators: =, <>, >, < and BETWEEN for character fields Like and Not Like is also supported. For Like and Not Like the percent sign (%) is used as wild card character to filter data:
The following example shows how the wild card character can be used:

Include only rows staring with A: A% Include only rows ending with A: %A Include only rows exactly equal to A: A Include only rows containing A: %A%
If you select filters for multiple columns all criteria’s must be met to include the row (and).
To remove all filters click the “remove all filters” button in the tool-bar

 

Q: I have a SQL query that I execute frequently. I often change the value in my where clause. Is it possible to change the value at execution time rather then changing the query every time? (Below is my query)

Select * from creator.Names Where name = ‘SMITH’

A: Sui supports prompted queries using symbolic variable resolution. To enable this function make sure that the variable substitution option is checked in the preference panel. Then change your query to:
Select * from creator.Names Where name = ‘&LAST_NAME’
When you execute this query Sui will ask you for the value of LAST_NAME (and replace &LAST_NAME with the value you supply). The value you supplied for a variable is kept and is displayed initially the next time you use the same variable. If you want to, you can use multiple variables. For example: Select * from &CRE.Names where name = ‘&LAST_NAME’ and ID between &LOW and &HIGH
In this example you are prompted to supply values for the table creator, the last name and the low and high values. Note that &LOW and &HIGH must be declared as numerical columns in the table.

 

Q: In the preference menu I have seen an option called “Intelligent field length”. What is this and what difference does it make if I enable it or not?

A: If this option is set the length of each column in the report window is optimised based on the actual length of the field rather then on the declared length of the field. If you for example have a field that is declared as CHAR(40) but no one of the rows you have selected for the specific column contains more the 10 bytes, the length for this column is initially optimised for 10 bytes.

The downside of enabling this option is that Sui has to check the length of each individual column for each row. This implies a query performance impact. It also affects sorting and filtering performance. In most cases the increase in response time is negligible.

 

Q: What is the schema list option and what effect does it have?

A: If you have selected the schema list option a combo-box with all available schemas is presented when you invoke the table or procedure list option (in the filter dialog). If you haven’t selected this option you have to enter the schema in a text field.

The downside of enabling this option is that Sui has to read all schemas every time you invoke the list table or list procedure options. If you are connected to a database with a large number of schemas this might have a negative performance impact.