Countries by ISO code

A list parameter in practice: pass several codes at once, each sent to the database as its own typed value.

When to use it

• Look up a list of keys from a ticket in one call.
• See how LIST OF TEXT becomes IN (?, ?, ?).

The Script

Download world-countries-by-codes.sql

-- @description: Countries by ISO code
-- @tags: world, sample, geo, list
-- @status: stable
-- @access: read
-- @dialect: H2
-- @param codes LIST OF TEXT "Three-letter country codes"
SELECT CODE, NAME FROM COUNTRY WHERE CODE IN (${codes}) ORDER BY CODE;

Parameters

Install and run it

1. Save the file in your Scripts Library folder (scripts in the installation folder by default), for example as gallery/world-countries-by-codes.sql.
2. Open the sample database: CONNECT WORLD;.
3. Check what it declares: LIB SHOW gallery/world-countries-by-codes.sql;
4. Run it: @gallery/world-countries-by-codes.sql codes=('FRA', 'DEU', 'ITA');

What you get

One row per code found, with the country name, sorted by code.

@countries/by-codes.sql codes=('FRA', 'DEU', 'ITA');
SELECT CODE, NAME FROM COUNTRY WHERE CODE IN (${codes}) ORDER BY CODE
-- codes = ('FRA', 'DEU', 'ITA')
|----|----------------------------------------------------|
|CODE|NAME                                                |
|----|----------------------------------------------------|
|DEU |Germany                                             |
|FRA |France                                              |
|ITA |Italy                                               |
|----|----------------------------------------------------|

3 rows fetched in 0 ms.

Limits

• A list holds 1 to 1000 values, without NULL.
• One value is still written as a list: codes=('FRA').

Related

• @
• Save a query with parameters and run it by name

Found something wrong or unclear on this page? Tell us, with the page address.