KDB string concatenation with symbol list for dynamic query

Question

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.

Answer 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.

Answer 2

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

Answer 3

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.