2

In this link, there is an example on how to include a dynamic parameter. d, in a KDB select query:

h: hopen`:myhost01:8012 // open connection
d: 2016.02.15 // define date var
symList: `GBPUSD`EURUSD
h raze "select from MarketDepth where date=", string d, ", sym in `GBPUSD`EURUSD" // run query with parameter d

Here d is of type date and is easy to string concatenate in order to generate a dynamic query.

If I want to add symList as a dynamic parameter as well by converting to string:

raze "select from MarketDepth where date=", string d, ", sym in ", string symList

The concatenated string becomes: select from MarketDepth where date=2016.02.15, sym in GBPUSDEURUSD, in other words the string concatenation loses the backticks so the query does not run. How can I solve this?

p.S: I know about functional querying but after failing for 2 hours, I have given up on that.

Thomas Smyth - Treliant
  • 4,993
  • 6
  • 25
  • 36
Zhubarb
  • 11,432
  • 18
  • 75
  • 114

3 Answers3

7

No need for functional selects.

q)MarketDepth:([] date:9#2016.02.15; sym:9#`A`B)
q)d:2016.02.15
q)symList:`B

q)h ({[dt;sl] select from MarketDepth where date=dt,sym in sl};  d; symList)
date       sym
--------------
2016.02.15 B
2016.02.15 B
2016.02.15 B
2016.02.15 B
Ryan Hamilton
  • 2,601
  • 16
  • 17
1

You are right, string SYMBOL does not preserve a backtick character, so you'll have to append it yourself like this:

symList: `GBPUSD`EURUSD
strSymList: "`",'string symList / ("`GBPUSD";"`EURUSD")

I used join , with each-both adverb ' to join a backtick with each element of a list. Having your symbol list stringified your dynamic query becomes

"select from MarketDepth where date=", (string d), ", sym in ",raze"`",'string symList

You can also use parse to see how a shape of a functional form of your query will look like.

q) parse "select from MarketDepth where date=", (string d), ", sym in ",raze"`",'string symList
(?;`MarketDepth;enlist ((=;`date;2016.02.15);(in;`sym;enlist `GBPUSD`EURUSD));0b;())

Now it's easy to create a functional select:

?[`MarketDepth;enlist ((=;`date;2016.02.15);(in;`sym;enlist symList));0b;()]

Hope this helps.

Update: @Ryan Hamilton's solution is probably the best in your particular scenario. You can even make a table name an argument if you want:

h({[t;d;s]select from t where date=d,sym in s};`MarketDepth; d; symList)

But it is worth noting that you can't use this technique when you need to make a list of columns dynamic. The following will NOT work:

h({[c;d;s]select c from t where date=d,sym in s};`time`sym; d; symList)

You will have to either build a dynamic select expression like you do or use functional forms.

Thomas Smyth - Treliant
  • 4,993
  • 6
  • 25
  • 36
Igor Korkhov
  • 8,283
  • 1
  • 26
  • 31
  • Thank you, I spent hours on the functional select as that seems the proper way to do it. However, the parsed tree throws error when I try to run the query. I managed to run it for simpler tables but when one of the where conditioned columns is of type symbol, it somehow doesn't work.. – Zhubarb Feb 19 '16 at 13:49
  • @Zhubarb: does a functional select I shown not work? – Igor Korkhov Feb 19 '16 at 13:56
  • I haven't tested it, but this one I failed to get to work. Table: `t:([] col1:8?`a`b; col2:til 8; col3:8?9.)`. And when I parse this: `select from t where col1=`a, col2<4`, I get `?[`t;enlist ((=;`col1;enlist `a);(<;`col2;4));0b;()]`, which doesn't give the expected result (i.e. gives a row where col1 is b). (Sorry the backticks mess the formatting up) – Zhubarb Feb 19 '16 at 14:04
  • 1
    I see. `enlist` in the constraint is what causing the problem. I don't know why `parse` generates it. A proper functional form is this: ```?[`t;((=;`col1;enlist `a);(<;`col2;4));0b;()]``` – Igor Korkhov Feb 19 '16 at 14:11
  • Yes, thank you! When I removed the outer enlist for the where conditions, it worked. It is very unhelpful that when I `parse` the query string, it creates it with the outer enlist though. – Zhubarb Feb 19 '16 at 14:18
  • Sorry, just a final question: I tried the each-both adverb solution above to the case where `symList` has a single element, i.e. `symList: *backtick*GBPUSD`, and realised it fails. Can I get it working for `symList` with single and multiple symbols using a generic line? – Zhubarb Feb 19 '16 at 17:07
  • 1
    `symList` must be a list even when it has just a single element, i.e. ```symList: enlist `GBPUSD```. – Igor Korkhov Feb 19 '16 at 18:25
0

Others have already given good alternative approaches for your problem. But in case if you need to join string and symbols (or other data types) without losing backtick, function .Q.s1 does the task.

q) .Q.s1 `a`b
q)"`a`b"
q)"select from table where sym in ",.Q.s1 symlist

Note: Generally it is not suggested to use .Q namespace functions.

Munawir
  • 3,346
  • 9
  • 33
  • 51
Rahul
  • 3,914
  • 1
  • 14
  • 25