- SQL-based Auto Complete

SQL-based Auto Complete

[PRO] In the previous examples, once all of the possible values were selected from the database Java was used to filter out those not matching the value entered by the user. In most cases this is adequate, however in cases where there are a large number of values to be filtered, doing the filtering in Java may be a performance issue. It is possible to use SQL to select only values matching the user’s entry from the database; this can improve performance but increases the complexity of the possible values SQL.

Using SQL-based Auto Complete

In order to use SQL-based filtering, you need to set the type property in the Auto Complete property group to SQL:

AutoComplete{

type: SQL;

}

Additional AutoComplete properties group attributes can be found here: - Auto Complete Properties

This informs WOW that the SQL in the possible values operation will take the value entered by the user into account, and therefore it is not necessary to filter the rows returned from the database.

The main query will be exactly the same for both SQL-based and Java-based auto complete. We will use the query from the first example in this section, where the user was searching for county names by entering a state. The SQL for that query was:

SELECT * FROM jetemp.COUNTY WHERE CNTSTATE = ?

In the Java-based auto complete scenario, the SQL for the possible values query was:

SELECT STCODE, STNAME FROM JETEMP.STATES ORDER BY STNAME

For an SQL-based auto complete scenario, we need to change the possible values SQL to be:

SELECT STCODE, STNAME

FROM JETEMP.STATES

WHERE STNAME LIKE ??CNTSTATE

OR CAST(??1 AS CHAR(10)) IS NULL

ORDER BY STNAME

This possible values operation will search a file containing both the state abbreviations and the full state names, based on a partial state name entered by the user. The partial state name will be pulled from the CNTSTATE field, which is the field that will be displayed on the screen by the main search query. Including the user’s value in the SQL means that all rows returned by this query can be shown to the user in the auto complete drop down – no additional filtering in Java is required. All possible values operations for SQL-based auto complete fields should use a LIKE comparison in the SQL, since the goal is to find all matches which begin with (or contain) a value entered by the user.

Derived Fields

If you are using SQL-based auto complete, then the possible values SQL query will use the LIKE comparison, which means that only a String based field can be used as the auto complete field. In order to use SQL-based auto complete on a non-character column in the database a derived field is required. (A derived field a logical field within WOW which does not directly correspond to a database field.) A derived field can also be used in cases where you want to use auto complete on a field while searching, but not when editing that field in a row.

In order to demonstrate using a derived field with auto complete, we will consider a case where we want to search for an account by account number. The account number is stored as DECIMAL data in the database, so we will need to use a derived field in order for auto complete to work. We will display the account number and the name of the account owner in the auto complete drop down.

The first step is to create a derived field descriptor in the table we are querying. You can name this field descriptor whatever you want, however you should make a note of both its name and its ID. The database type and type name should both be set to CHAR – this will cause the field to be created as String field. Ensure that the size of this field is adequate to hold whatever values may be displayed/entered by the user in the search field. (In our case we need to make the field big enough to hold the account number plus the name of the account owner.)

For the main query, we will want the prompting to use the derived field (which is a String field) as opposed to the normal field for that database column (which is not a String field). This is accomplished by using the ID of the field descriptor in the operation query. In this case, the field descriptor’s ID is 711628. We will also have to use the SQL CAST function in order to compare the character data in the derived field to the non-character data in the database field.

Next, we create the possible values operation as usual for the auto complete field. The possible values operation should refer to the value in the derived field, since that is the field displayed on the screen where the user will be entering values into. In our example, our derived field is the ID_AUTO_COMPLETE field. This operation will probably also have to use the CAST function to convert the non-character data in the database to CHAR data.

Finally, return to the derived field descriptor created in the first step, and set its display component to Auto Complete, and its possible values operation to the possible values operation created earlier. (The possible values operation did not exist when we first created the derived field descriptor, or else we would have set it then.)

Now our auto complete search field is set up. As the user types in an account number, the matching account numbers along with the account owner’s name is displayed in the drop down.

Auto Complete Fields in Rows

So far, the examples in this section have focused on using auto complete fields as parameters in a query. This is the most common scenario where auto complete fields will be used. However, if an auto complete field is selected as the result of a query, then the displayed field will retain its auto complete behavior in the results. If you only want to use auto complete during the query prompting and not after the field is selected, then you can use a derived field with auto complete for the query prompting, in which case the fields in the results will not use auto complete. See the Derived Fields heading above for more information on using derived auto complete fields.

If you do want to have auto complete fields in your results, and you are using SQL-based auto complete, then you may need to make further adjustments to the possible values query in order to show the correct display to the user. To demonstrate this we will look at the very first auto complete example from above. The main query was selecting a list of counties by state.

Here is the SQL:

SELECT * FROM jetemp.COUNTY WHERE CNTSTATE = ?

The first page of results when we run the query looks like this:

Notice that the state column shows the internal value “MN” instead of the display value “Minnesota”. When an SQL-based auto complete field appears in the results, by default WOW will show the internal value. This is not a problem if the display and internal values are the same, but in this case they are different, and we want WOW to show the display value. The possible values SQL for the CNTSTATE field (which is the auto complete field) is:

SELECT STCODE, STNAME

FROM JETEMP.STATES

WHERE STNAME LIKE ??CNTSTATE

OR CAST(??1 AS CHAR(10)) IS NULL

ORDER BY STNAME

The STCODE column contains the internal values, which in our case are state abbreviations like “MN”. The STNAME column contains the display values, like “Minnesota”.

In a non-auto complete scenario, WOW uses the possible values operation to convert the internal value into a display value. However for an SQL-based auto complete field, the possible values operation is used to convert a partial display value (entered by the user) into an internal value. The “normal” non-auto complete possible values query for this field would probably look like this:

SELECT STCODE, STNAME

FROM JETEMP.STATES

ORDER BY STNAME

In cases like our example we need the possible values operation to do one of two things, depending on whether or not the field is currently being used for an auto complete lookup, or if the field is just being displayed to the user.

The two possible values queries listed above are the same, except for the WHERE clause. So we can combine the two queries into a single possible values query by using the correct WHERE clause:

SELECT STCODE, STNAME

FROM JETEMP.STATES

WHERE STNAME like ??CNTSTATE

OR CAST(??1 AS CHAR(10)) IS NULL

OR NOT (??*AUTO-COMPLETE)

ORDER BY STNAME

Notice the special ??*AUTO-COMPLETE parameter. When the possible values query is being used for auto complete purposes, this value will be true, and therefore the WHERE clause will filter rows for the auto complete query. In other cases, such as retrieving the display value for an internal value, ??*AUTO-COMPLETE will evaluate to false, and the WHERE clause will not filter out any rows.

By constructing a possible values query which uses the ??*AUTO-COMPLETE parameter in the WHERE clause, your possible values query can be used for both auto complete and non-auto complete scenarios. After changing the possible values query as described above, rerunning the main query gives these results: