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]
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
selectquery returns a table, so all columns must be the same length. - An
execquery 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.
Q for Mortals
§9.4 The exec Template