exec vs select — Extracting Lists, Scalars, and Dictionaries

Question

Given:

t:([] sym:`AAPL`MSFT`GOOG`AAPL; price:185.5 410.1 142.8 187.0; qty:100 200 300 150)

Show the difference between select and exec by:

1. Returning the sym column as a plain list (not a table)
2. Returning the sym and price columns as a dictionary
3. Returning the average price as a scalar atom

Solution

/ 1. Plain list
exec sym from t
/ `AAPL`MSFT`GOOG`AAPL

/ 2. Dictionary
exec sym!price from t
/ AAPL| 185.5
/ MSFT| 410.1
/ GOOG| 142.8
/ AAPL| 187

/ 3. Scalar
exec avg price from t
/ 231.35

Explanation

select always returns a table — even when only one column or one row is requested:

select sym from t
/ sym
/ ----
/ AAPL
/ MSFT
/ GOOG
/ AAPL

select avg price from t
/ price
/ ------
/ 231.35

exec unwraps one level:

Extracting a list is the most common use — whenever you need a vector to pass to another function, exec saves you the indexing step:

/ select gives a table; must index to get the list
syms: (select sym from t)`sym

/ exec gives the list directly
syms: exec sym from t

Dictionary form uses ! between two column names inside exec. Left column becomes the key, right becomes the value:

exec sym!price from t
/ AAPL| 185.5   / note: duplicate keys are kept; a lookup returns the first match
/ MSFT| 410.1
/ GOOG| 142.8
/ AAPL| 187

Aggregation with by — with a by clause, exec returns a dictionary keyed by the group values:

exec avg price by sym from t
/ AAPL| 186.25
/ GOOG| 142.8
/ MSFT| 410.1

Compare with select avg price by sym from t, which returns a keyed table (same data, different type).

When to use exec:

• Feeding a result into another function that expects a list or dictionary
• Extracting a single value directly, rather than as a one-row table
• Building dictionaries dynamically from table columns