SQL database can be used as a data source of the lookup list. Then the list values are linked with specific columns in the database table by using SQL query.
When retrieving values to the lookup list by using an SQL Query, you need to ensure that the value selected from the combobox property still matches with the value that comes from the database. If the values does not match, the comboboxes behave the following ways:
| Combobox | The value selected earlier from the list of combobo will be set as empty if the new value in database does not match with the selected one anymore. |
| Editable combobox | The new value that comes from the database overrides the existing value if the values does not match. |
| SQL query to retrieve values |
Specifies an SQL query to be used to retrieve the values from the database into the lookup list.
Allowed count of columns for the list types: |
|
| Simple | Retrieve one column. | |
| Key-Value Lists | Retrieve a one value column and up to 15 key columns for them. | |
| Hierarchical List | Retrieve several columns from from the data source in hierarchical order. | |
|
The lookup list cannot be saved if the SQL query retrieves more or less columns than allowed for the selected list type.
|
||
| Example SQL Query | ||
| SELECT name, display_name FROM ct.v_users | ||
|
An example query above retrieves values of columns name and display_name from the database table called ct.v_users. A key-value in this example case is the value retrieved from the name column.
|
||
| Another SQL Query Example | ||
|
SELECT * FROM (
SELECT kdi AS id, '' AS parent, RIGHT('0000' + ltrim(str( kdi )), 4) + '. ' + teksti AS value, 0 AS sort_order, kdi AS [key] FROM [NOVA LINK].[YR0].[dbo].[v_tuoteryhma] WHERE paakdi IS NULL UNION SELECT kdi AS id, paakdi AS parent, RIGHT('0000' + ltrim(str( kdi )), 4) + '. ' + teksti AS value, CASE WHEN paakdi IS NULL THEN 0 ELSE paakdi END AS sort_order, kdi AS [key] FROM [NOVA LINK].[YR0].[dbo].[v_tuoteryhma] WHERE teksti IS NOT NULL AND teksti <> '' AND paakdi IS NOT NULL ) AS rs |
||
|
An example query above retrieves values of the columns located in an external database in hierarchical order so that the SQL Query result could be used as a data source of a hierarchical lookup list.
Hierarchy of the list values is based on the column called in results as sort_order which is always zero (0) on the very first level. On the other hierarchy levels, the sort_order value comes from the database table column called paakdi if its' value is not empty, otherwise the sort_order is zero (0) also on the other levels of hierarchy. All key values in the results are formatted so that the final values retrieved from the database are four (4) characters long. Zeros are added into the beginning of column values until the length of four characters is reached in all values. Values you can retrieve e.g. into the combobox type of Properties using this SQL Query comes from the database columns called kdi (id/key), paakdi (parent) and teksti (value). A text inside the brackets on the column names above are the names of the colums the values are retrieved from the database table using the example query. |
||
| Test SQL |
Executes the SQL query which is specified in the SQL query to retrieve values box.
If the query works properly, the results of the query appears into the value table. You can also select the desired part of your query to execute only that part of SQL query. TIP: Visit MSDN for further help with SQL queries. |
|
|
TIP: Right-click the desired value in the lookup list value table and select Copy to copy the selected value to the Windows clipboard so you can paste the value into another places. |