Re Queries

Community Support
1

Hi, very short question here, creating a function that takes a table, column(s), and a sym to filter by, i have the function in the correct order, however the column names are passed into the functions as symbols and i dont know how to change them in such a way so that the function can use them 

 

2

Hi conorn_7,

The q-sql example provided is guidance for what the function should achieve, however you’ll need to write a functional select to handle the dynamic columns being passed into the function.

Check out code.kx for some examples.

Hope this helps,

David

3

This can be a rabbit hole for newcomers, so I’m going to expand on David’s answer, and hope to provide some light entertainment for others too, who will perhaps be able to add something useful as well.

Background  qSQL is a major feature of q, which was developed as a wrapper around the k language. Q inherits from k a functional syntax for queries. It is terse and powerful but complex queries quickly become hard to read. The more limited qSQL query syntax is much clearer – and just as fast! But as David says, qSQL does not let you substitute variables for column names.

You can either work around this or dive in and write your query in functional form. First the workaround.

Workaround  Like any language processor, the q interpreter parses your expressions. The result of parsing is a parse tree. Here is the parse tree for 6*7: (*;6;7). Simply a list: a function followed by a list of its arguments. The parse keyword returns a parse tree from a string expression. The eval keyword evaluates a parse tree. Basically, you have the q parser and interpreter at your disposal.

q)eval(*;6;7) 42 q)eval(+;3;(*;6;7)) 45 q)parse "3+6*7" + 3 (*;6;7)

So you can work around qSQL’s limitation.

q)show trade:([]sym:10?`MSFT`APPL`GOOG;price:10?10.0) sym price ------------- MSFT 3.927524 APPL 5.170911 GOOG 5.159796 GOOG 4.066642 APPL 1.780839 MSFT 3.017723 APPL 7.85033 GOOG 5.347096 MSFT 7.111716 APPL 4.11597 q)select price from trade where sym = `MSFT price -------- 3.927524 3.017723 7.111716 q)eval parse"select price from trade where sym = `MSFT" price -------- 3.927524 3.017723 7.111716 q)getColForSym:{eval parse raze("select ";" from ";" where sym = `"),'string y,x,z} q)getColForSym[`trade;`price;`MSFT] price -------- 3.927524 3.017723 7.111716

Functional form  So much for the workaround. Now look at a functional query. We’ll build it up from the simplest form, studying the Reference.

q)/select from trade q)?[trade;();0b;()] sym price ------------- MSFT 3.927524 APPL 5.170911 GOOG 5.159796 GOOG 4.066642 APPL 1.780839 MSFT 3.017723 APPL 7.85033 GOOG 5.347096 MSFT 7.111716 APPL 4.11597 q)/select price from trade q)?[trade;();0b;{x!x}enlist`price] price -------- 3.927524 5.170911 5.159796 4.066642 1.780839 3.017723 7.85033 5.347096 7.111716 4.11597 q)/select price from trade where sym = `MSFT q)?[trade;enlist(=;`sym;enlist`MSFT);0b;{x!x}enlist`price] price -------- 3.927524 3.017723 7.111716

Now we can write getColForSym.

q)getColForSym:{?[string x;enlist(=;`sym;enlist z);0b;{x!x}enlist y]} q)getColForSym[`trade;`price;`MSFT] price -------- 3.927524 3.017723 7.111716

The function needs more work to handle multiple columns and symbols. Start by replacing = with in. You might also want to check that the table contains the columns requested.