The Select Statement Dialog |
The SELECT statement is opened from the connection dialog by creating a statement on a connection, refer to . The description below includes the criteria processing of the SELECT statement, which comprises of the static (WHERE clause), the dynamic statement processing, JOIN clause and no WHERE processing. This dialog below is opened in one of three ways:
|
![]() |
Number (1) shows the button area of the statement dialog. This area has buttons for
undocking (floating) the window from the tabbed area, or putting it back into the
tabbed pane if it is floating (these are the two blue arrows which are enabled according
to the current state of the window). Furthermore we have the zoom buttons (magnifying
glass) which are only valid if the dialog is floating. When the window is floating
it cannot be resized by grabbing the borders. The only way to resize is to use the
magnifying glass icons. The dialog can be resized freely when it is docked inside the
tabbed pane, by using the split panes. Number (2) is pointing at the list of tables and columns for the connection of this statement. We have the ability to select a table, which selects all the columns of that table or we can select single columns. The selection and deselection are done by clicking on the red 'x" or green arrow. Every column selected here is automatically put into the SQL string at number (5). At number (3) we can see the list boxes which allow us to select a SQL type for the select. The default is the same as the actual type of the column in the database. There are cases where this value needs to be changed to overcome issues with the database drivers.One example is the PostgresSQL driver which requires VarChar to be selected as for the MONEY type. Another example is the FLOAT to DOUBLE conversion for some index types. Number (4) shows a list of the types of tables that can be seen from the data source. The most common type that is worked with is the USER TABLE, which shows the tables created by users. There is an exception to this, and that is for EXCEL worksheets, where the user tables (sheets) are stored under SYSTEM. Below number (4) we can see the selection boxes for the type of table objects that can be seen in the list above. The filter icon is used to filter all names with "$", "#' or "_" in them. This is mainly used with Oracle databases. Number (6) shows the button which allows for setting the type of SQL formatting to use in the string. The arrows here show the button and the actual dialog opened on the button. Each database has its own format, so we accommodate this via this panel. The setting here is automatically set from the driver name and is normally correct. If not, the settings can be changed as to the character and the type of quotation. The button at number (7) allows for manual editing of the SQL string. The string once changed is stored under the user string. If this button is toggled the string will also toggle between the formatted string (from the tables and columns selected) and the user string. The status of this button indicates which string is to be used for the SQL statement. The 'Ext' edit box next to the manual edit button allows for entry of 'external' clauses to be added to the formatted (not manual) SQL string. It is only enabled when the manual edit button is disabled. This is useful for ORDER BY or GROUP BY clauses at the end of the SQL string. Note: formatting must be entered by hand for the specific database driver. In other words, if MS -SQL is the source then '[' and ']' need to be put around the table and column names entered in here. Tip : Just follow the formatting of the SQL statement in the edit box at number (5). Number (8) shows the selections for result set CURSOR movement and capabilities. For simple processing of the records, from beginning to end, the 'Forward Only' is sufficient. If capabilities like counting records and dynamic updating are required then Scroll Sensitive should be utilised, before Scroll Insensitive. The updatable option should be selected when the values of the result set are to be changed and committed back to the database. Number (8) also contains a list of CHARSETS available in the system. This selection is also inherited by the RowSet of dbJAPI, which allows for changes at any time during and after reading the records (like in the View Data Dialog). At number (9) we have buttons Viewing the data with all its relative resultsets, generating code and clearing the form. The CodeIt! generates the code of all the connections and statements declared to this point. A window opens with the code (click here for more information). The criteria dialog below is opened with the criteria button. The clear button deselects all the selected tables and columns, and therefore clears the SQL string. The view data button is accompanied by text boxes for specifying how many rows are to be viewed in the data dialog () In the first field we specify the first record number to be returned in the result set.The rows field holds the number of records to be processed to the view data dialog. There is a limit of 1000. The value entered here applies to all levels of a dynamic result set. In other words is we have CUSTOMERS linked to TRANSACTIONS, in a one to many relationship (primary dynamic), and we specify 10 here, then only 10 customers will be show, with their first 10 transactions each. Please note that these figures are not stored in the settings of the project if saved. They are purely for display purposes of the VIEW DATA panel. |
No Criteria![]() |
When we select the criteria button from the select
statement dialog the above dialog opens. It is a model dialog, so it does not allow for
other windows to be opened until it is closed, except for the data view panel which is
opened from within. There are four radio options (1) at the top which allow for specifying no criteria (no WHERE clause), static criteria (WHERE clause with static values), dynamic criteria (WHERE clause with dynamically changing data from another statement's result set) and a JOIN clause. Number (2) points to the different areas of the screen, which are used to depict and control the data for the various criteria types. At the moment we have chosen not to process criteria by selecting NONE. All the areas of the dialog are disabled and the SQL string for the statement is shown at the bottom of the screen (3). |
Dynamic Criteria![]() |
Above we can see a typical CUSTOMER and ACCOUNT relationship, which is actually
a dynamic criteria type between the ACCOUNT and CLIENTS statements. What this
actually means is that for every record (row) of the CLIENTS statement, the
ACCOUNTS statement is executed with data from that row in the WHERE clause and
a new result set is created. The ACCOUNT result set is linked to the CLIENT in
db-JAPI and the implementing application can easily interpret and utilise the data, which
can have multi-level relationships. In the above relationship the CLIENTS statement is known as the PRIMARY statement and the ACCOUNT statement is the dynamic. There is no limit to the levels of relationships, which means that a statement can be both dynamically executed while providing data to another sub-statement (dynamic) as a primary. Number (1) requires that dynamic be selected for processing dynamic statements. The statement to be executed with the data from another statement is the dynamic statement and holds the criteria of the relationship. The primary does not contain criteria data of the sub-statements that it is providing data to. Number (2) shows a list of statements in the system. To be able to create a dynamic statement, the primary statement must exist or have been pre-created. Selecting a statement from this list causes the columns of that SELECT statement to be shown in the data provider list under it (as shown by number (3)). Number (3) also shows the selected statement columns for the current statement. We then need to select a relationship between the two so that a WHERE clause is created for the selection of the relative records. In this case we have specified that we want to SELECT all ACCOUNTS that have the CLIENT ID of the current record of the CLIENTS statement. This will provide the accounts for every client that is selected in the client statement. When we select from the lists at (3) the area depicted by (4) is updated with the values selected. The column field shows the TABLE.COLUMN and the criteria field shows FOREIGN_PRIMARY_STATEMENT.TABLE.COLUMN. We then need to select the operation for the WHERE clause. In this case we select EQUALS (5). Number (6) shows allows for static value substitution, which means that the SETOBJECT of the underlying PREPARE STATEMENT is bypassed, by taking the value from the primary statement and actually putting it into the WHERE clause as if it was written by hand. This is required for some data sources, like EXCEL for certain types of data. If you find that the statement is not returning the correct result set, try selecting this. After setting all the values for (4), (5) and (6) we can proceed to (7), which allows us to add these settings to the WHERE clause by hitting the '+' button. To remove an unwanted entry we need to select it from the table, show by the long arrow of (7), and then hit the '-' button. The SQL statement, as it is being edited, is depicted in the area show by (8). The preview button allows us to see the multi-level result of the criteria specified. Only the first 100 records are shown. If this does not suffice, go to the SELECT STATEMENT dialog and select VIEW data with the number of records required. |
Static Criteria![]() |
Moving on to the STATIC criteria type, we notice that the parts of the screen are not
utilised as with the dynamic. This makes it much easier to set as we just need to specify
the columns, operations and criteria for a STATIC WHERE clause. At number (1) we need to specify the Static Criteria. With that the Selected Statement becomes available for selecting the column to use in the WHERE clause. In the snapshot above we notice that the ID field has been used twice with IN and BETWEEN operations. We can use any columns in the selected statement and specify any number of clauses for the WHERE statement. You may ask how it is possible to add many criteria values to a single column with the IN or BETWEEN operations. Well we utilise the little '+' button shown by number (3) after entering a value in the criteria combo box text area. In the same manner we could choose a value in the combo box drop-list and use the '-' button to delete it. Once call the values are entered in the criteria list and the operation is correct, the '+' button shown by (5) can be pressed to add the complete clause to the WHERE statement, as shown by (6). Number (4) also allows the selection of the mathematical AND/OR operator for the joining of the clauses of the WHERE. The first clause does not have a AND/OR operator, but from there on out we need to specify either so that the SQL processor knows how to process the clauses. |
JOIN Clause![]() |
The Join capability allows for JOIN clauses to be added to the
SQL statement. It provides the ability to have JOIN clauses with ON or
USING or no parameter. This can be specified using the two checkboxes under the Join
radio at (1). If no boxes are selected then NONE is implied and only the
JOIN with the tables is added to the SQL statement. When selecting ON or USING the dialog changes as indicated below, with (1) for NONE, (2) for ON and (3) for USING. After specifying the parameters as described below, we can use the '+' button at (5) to add the specified JOIN clause to the table at (6) and therefore to the the SQL statement shown at (7). The save and preview buttons allow us to save and then see the data output of the SQL statement. Any errors are reported to the Log window at the time of preview. Only the first one hundred records of each level of the result set(s) is shown in this preview. |
![]() |
Above we can see the three different dialogs for the JOIN types. As mentioned above,
the first is for type NONE, the second is for type ON and the third is for type USING.
Number (1) indicates the tables chosen for the JOIN. These tables are chosen by selecting
columns from the two tables for LEFT and RIGHT. Number (2) shows the columns and tables chosen for the ON clause of the JOIN. If the operation selected is BETWEEN or IN then multiple columns can be selected as the second part (right side) of the ON combo boxes. We do this by using the little '+' or '-' sign next to Clients.id in the image above, after selecting a column from the RIGHT table above. The combo list shows all the entries chosen for the operation. At the point of having selected all the required columns, the green '+' to the right can be pressed for adding the clause to the JOIN. The clause is added with the AND/OR selected above the operation. This applies for the 2nd clause onwards. Number (3) shows the tables involved in the USING clause, and also shows the column name (which is identical to both tables) underneath the tables. More than one column can be selected with the SHIFT or CNTRL buttons and the mouse on the RIGHT TABLE. |
![]() |