Skip to content

exec

Return selected rows and columns from a table

exec is a qSQL query template and varies from regular q syntax

For the Exec operator ?, see Functional qSQL.

Syntax

In the following syntax, square brackets mark optional elements.

exec [distinct] ps [by pb] from texp [where pw]

qSQL syntax

From phrase

The table expression texp can be a table in memory or on disk. A table on disk can be splayed but not partitioned. On a partitioned table, exec signals a nyi error.

The workaround is to use the result of a select query as the table expression:

exec … from select … from …

Select phrase

Where the Select phrase

  • is omitted, returns the last record
  • contains a single column, returns the value of that column
  • contains multiple columns or assigns a column name, returns a dictionary with column names as keys
q)sp:([]s:`s1`s1`s1`s1`s4`s1`s2`s2`s3`s4`s4`s1;p:`p1`p2`p3`p4`p5`p6`p1`p2`p2`p2`p4`p5;qty:300 200 400 200 100 100 300 400 200 200 300 400)

q)exec from sp  / last record
s  | `s1
p  | `p5
qty| 400

q)exec qty from sp  / list
300 200 400 200 100 100 300 400 200 200 300 400

q)exec amount:qty from sp  / assigns column name
amount| 300 200 400 200 100 100 300 400 200 200 300 400

q)exec (qty;s) from sp  / list per column
300 200 400 200 100 100 300 400 200 200 300 400
s1  s1  s1  s1  s4  s1  s2  s2  s3  s4  s4  s1

q)exec qty, s from sp  / dict by column name
qty| 300 200 400 200 100 100 300 400 200 200 300 400
s  | s1  s1  s1  s1  s4  s1  s2  s2  s3  s4  s4  s1

q)exec sum qty by s from sp  / dict by key
s1| 1600
s2| 700
s3| 200
s4| 600

q)exec q:sum qty by s from sp  / xtab:list!table
  | q
--| ----
s1| 1600
s2| 700
s3| 200
s4| 600

q)exec sum qty by s:s from sp  / table!list
s |
--| ----
s1| 1600
s2| 700
s3| 200
s4| 600

q)exec qty, s by 0b from sp  / table
qty s
------
300 s1
200 s1
400 s1
200 s1
100 s4
100 s1
300 s2
400 s2
200 s3
200 s4
300 s4
400 s1

q)exec q:sum qty by s:s from sp
s | q
--| ----
s1| 1600
s2| 700
s3| 200
s4| 600

Compare the results of select and exec queries with multiple columns:

  • A select query returns a table, so all columns must be the same length.
  • An exec query returns a dictionary, so column lengths can vary.
q)t:([]name:`tom`dick`harry`jack`jill;sex:`m`m`m`m`f;eye:`blue`green`blue`blue`gray)
q)t
name  sex eye
---------------
tom   m   blue
dick  m   green
harry m   blue
jack  m   blue
jill  f   gray
q)select name, distinct eye from t
'length
  [0]  select name, distinct eye from t
       ^
q)exec name, distinct eye from t
name| `tom`dick`harry`jack`jill
eye | `blue`green`gray

Limit expression

exec distinct returns only unique items in the first item of the result.

q)exec distinct s,p from sp
s| `s1`s4`s2`s3
p| `p1`p2`p3`p4`p5`p6`p1`p2`p2`p2`p4`p5

Cond

Query templates do not support Cond. For alternatives, see Cond in qSQL.


delete, select, update

qSQL, Functional qSQL

Q for Mortals §9.4 The exec Template