Value Lists Using SQL

You can use the SELECT and EXEC commands to retrieve parameter values, validation lists, and selection lists.

Here's a typical configuration in the xls.handlers table:

In the HANDLER_TYPE column, specify the CODE handler type and provide the SQL code in HANDLER_CODE.

For a basic setup, the SQL code might look like this:

SELECT id, name FROM s02.companies ORDER BY name, id

You can also incorporate parameters. For instance, to select values based on a user's language, you could use:

SELECT
    c.id,
    COALESCE(t.TRANSLATED_NAME, c.name) AS name
FROM
    s02.companies c
    LEFT OUTER JOIN s02.xl_translations t ON t.TABLE_SCHEMA = 's02' AND t.TABLE_NAME = 'strings'
            AND t.LANGUAGE_NAME = @data_language AND t.COLUMN_NAME = c.name
ORDER BY
    name,
    id

In this example, the context parameter @data_language is populated with the user's data language.

Note: SQL Server and SQLite use parameters prefixed with @, while other database platforms use a colon, like :data_language.

Useful Tips

The sample configuration above shows the same code in two rows. In real-world applications, you might see duplicated code across many rows.

To optimize this, consider these two approaches:

1. Create a stored procedure to encapsulate the code.
2. Define an SQL code object in the xls.objects table and reference it in the xls.handlers table.

Here's an example configuration for the second approach in the xls.objects table:

With this setup, you can use the defined s02.xl_list_company_id_code object without needing to redefine the code: