Home → Using LincDoc 3.1+ → Database Lookups → Defining a Lookup Query
19.6. Defining a Lookup Query
When you use the JDBC Advanced lookup type, the lookup configuration dialog box allows you to customize the database query using the large Query dialog box (highlighted below).
Proceed to one of the following sections below for more information on defining the query:
Defining the Query
The query is a standard SQL SELECT statement. Each column returned by the query can be mapped to a LincDoc field.
A special property of lookup queries is that you can use field names from your LincDoc form in WHERE clauses. Field names should be bracketed with double less-than and greater-than symbols (for example <<name>>).
Some example queries will help to explain the functionality. Let's say you wanted to lookup applicant information from a permit application based on the application number entered on your form in a field called app_number. You might enter a query like the following:
|
1
|
SELECT applicant, app_city, app_prov, total FROM demo.permit_app WHERE app_no=<<app_number>> |
You could then map applicant, app_city, app_prov and total to fields on your LincDoc form.
Let's say that the user completing the form does not know the application number and wants to choose an application from a list of HVAC permits filled out in the last 30 days. To do this, you would set the On multiple results option to Prompt to Select One and provide a query like the following:
|
1
|
SELECT app_no,applicant,app_city,app_prov,app_zip,hvac_fee,total,CURRENT_DATE-CAST(app_dt as date) as "age" FROM demo.permit_app where disciplines='HVAC' and (CURRENT_DATE-cast(app_dt as date))<=30 order by age |
Several interesting features are being used here.
- When the user is presented with a list of results to choose from, the columns in the list will appear in the order in which they're specified in the query.
- The SQL CAST() function is being used to convert the app_dt field from the timestamp type to the date type.
- The SQL CURRENT_DATE keyword is being used to calculate the number of days between now and the application date.
- The SQL "AS" keyword is being used to give the calculated column the name "age". You will notice that when you click refresh columns, you will see a column called age in the Query Result Column list, and you will be able to map this column to a field on your LincDoc form. Also, when the user is presented with a list of results to choose from, the column heading will use the names specified with the "AS" keyword.
- The SQL ORDER BY clause is being used to sort the results. You will notice that when the user is presented with a list of results to choose from, that list will be in the order you specify in the query.
The CAST() function is a good way to make sure that the data type of the database column matches the data type of the field on the form. For example, if the app_zip column in the database is an integer field and the zip code field on your LincDoc form is a string, you could use CAST(app_zip, VARCHAR(5)) to convert the column to a string type.
Let's say that you wanted to search for records based on a partial match of a person's last name. Assuming that your LincDoc form has a field called lastname and your database table has columns called person_first, person_last and person_city, you could use a query like the following:
|
1
|
SELECT person_first, person_last, person_city FROM demo.job_apps WHERE LOWER(person_last) LIKE LOWER(<<lastname>>) |
The first thing to notice is that we've made the search case-insensitive by using the SQL LOWER function on both the database column and the field from the LincDoc form. So a database record with the value "Smith" in the person_last column will be included in the query results regardless of whether the user types "smith", "SMITH" or "Smith" in the lastname field on the LincDoc form.
The second thing to notice is that we have allowed partial matches by using the SQL LIKE keyword. It is very important to note that using LIKE will perform exact matches unless there is at least one SQL wildcard character in the search string. So in our example above, if the user types "Smit" in the lastname field on the form, the query results will not include the record where the person_last column is "Smith". To make the partial match, the user will need to type something like "Smit%" in the lastname field. The percent sign wildcard matches multiple characters at any point in a string, so our "Smith" record would be included in the results if the user typed "s%h", "%mit%" or "%ith" in the lastname field on the form.
As a final example, let's say that your query is likely to return more than one result, but that you wanted to choose one of the results automatically rather than having the user pick from a list. You could accomplish this with a combination of an SQL ORDER BY clause and an SQL LIMIT clause, as in the following query:
|
1
|
SELECT app_dt,app_no,applicant,app_city,app_prov,app_zip FROM demo.app_permit WHERE disciplines=<<permit_type>> ORDER BY app_dt DESC LIMIT 1 |
This will cause only the first result to be returned, thus preventing a list of results from being presented to the user. In this case, the first result will be the most recent application because of the ORDER BY clause.
Note: Older versions of Microsoft's SQL Server do not support the LIMIT clause. To get the same functionality, you would use "SELECT TOP 1 ..."
Using DRAT Expressions in Your Query
You can use DRAT expressions (such as: <<fieldName>>,<<:doc-meta:uuid>) in the SQL. These expressions will be parsed before sending the SQL to the RDBMS.
In the following example, the DRAT expression is underlined and represents a patient's last name.
In this query example, the where clause uses the lower function to automatically make both sides of the comparison lowercase, even if they are added in uppercase. This step eliminates any case-comparison issues by removing the difficult to control variable of how an end user enters the information. In addition, some back end databases always store information in all uppercase. In short, this part of the query verifies that all possible matches are found for use in your form, regardless of the original entry case.
In addition, the rtrim functions removes unnecessary blank values, which can cause lookup issues, especially if a database pads the end of entries with blank values. For example, a lookup can fail since it is expecting "Smith" but it instead finds "Smith " and does not return the correct value for the lookup.
Finally, the percent signs ( % ) act as wildcard characters, allowing for the use of partial matches. For example, if a user enters "chest" for a town name lookup request, matches would be made with entries such as "Rochester", "Chesterville", etc.
For more information on DRAT, see Using Document Refinement Annotation Transformer (DRAT).
Using a Query with Multi-value Fields
When setting up a lookup using a multivalue field, it is important to note the syntax don't include the # inside of the lookup query. Take the example. Suppose that there are three fields in a multivalue group: description, upc, and price. In the database, the fields are item_description,item_upc, and item_price. In order to do a lookup for the price based on the entered UPC, the necessary query would be:
|
1
|
select item_price from price_table where item_upc = <<upc>> |
Normally, when working with multivalue fields, you would have done upc#. But that will not work with lookups.