SQL in KDB-X Examples

These examples show how to run SQL queries in KDB-X. They include query patterns, execution methods, parameter usage, integration with q, and error handling.

Run SQL

You can invoke SQL in several ways:

s) prompt
.s.e function
pgwire interface

Use `s)`

Use the s) prompt to enter SQL interactively. Prefix your query with s) to run SQL instead of q.

q)t:([]a: til 3)  // create a table t with a column a
s)select a from t

Example

q)trips:([]date:2#.z.D;city:`ldn`ny)
s)SELECT * FROM trips WHERE date=.z.D

Use `.s.e`

Use .s.e to run SQL in the KDB-X process command line. Prefix the SQL query with .s.e and wrap it in double quotes.

q)query:"select * from t"
q)result.s.e query
q)result
a
-
0
1
2

Parameter	Description
`result`	Result set from the query.
`query`	SQL statement wrapped in double quotes.

This example demonstrates how to store the results of one SQL query in a variable and reuse it in another query within KDB-X:

// select two fields and save them in variable x
x:.s.e"SELECT vendor, distance FROM trips WHERE distance<20"

// reuse x in another SQL statement
.s.e"SELECT * FROM x"

Parameters

Use parameters to provide values to predefined queries.

Execute directly

Use .s.sp to inject q type parameters into SQL queries. Use $n notation in the query.

parsedquery:.s.sp[query](parameter list)

This example shows how to execute a parameterized SQL query in KDB-X by injecting q values into placeholders using .s.sp. Here, the query selects rows from table t where s matches either AAPL or GOOG and p is greater than 12.34:

result:.s.sp["select a from t where s in $1 and p>$2"](`AAPL`GOOG;12.34)

Single parameter queries

Convert a single parameter into a list because .s.sp always expects a list.

.s.sp["SELECT vendor, fare FROM trips WHERE fare>$1"](enlist 50)

Prepare and execute

Use .s.sq to parse and prepare a parameterized query once. Then use .s.sx to execute it multiple times with different parameters.

parsedquery:.s.sq["select a from t where s in $1 and p>$2"](``;0n);
r1:.s.sx[parsedquery](`AAPL`GOOG;12.34)
r2:.s.sx[parsedquery](`MSFT`VOD;56.78)

SQL parse tree

Use .s.prx to display the SQL parse tree.

.s.prx"select * from trade where date='2021.11.23' and symbol in ('XBTUSD')"

Integrate with q

You can run q functions inside SQL statements or create SQL functions from q functions.

Use `qt()`

Use qt() to call a q function that returns a table. You can only call qt() in the FROM clause.

s)select a from qt(f,x,y...) where ...

This example shows how to query the result of a q function directly from SQL using qt():

s)select a from qt('{gettable[`$x;"D"$y;z]}','AAPL','2001.01.01',1)

Use `q()`

Use q() to call q functions anywhere in a statement and return any q data type.

s)q(t,f,x,y..)

This example demonstrates how to use the q() function within an SQL statement to invoke q code directly and return its result:

s)select q('J','test',10,col) from t

Convert a q function into a SQL function

Call .s.F to wrap a q function as a SQL function.

`.s.F[`functionname]:.s.fx{code}

This example shows how to expose a q function so it can be called directly from SQL queries in KDB-X:

.s.F[`fun]:.s.fx{x+1}
s)select fun(x) from qt('([]1 2 3)')

Error handling

SQL in KDB-X raises errors when you reference columns that do not exist or use unsupported syntax.

SELECT non_existing_column FROM trades;
-- Error: column not found

Next steps

Learn about supported features in the Reference page.
Follow the Quickstart guide for step-by-step setup.