Search code examples
kdb

select query with where condition using variables instead of column names in q kdb

I have a table with columns sym, px size

t:([] sym:`GOOG`IBM`APPL; px:10 20 30; size:1000 2000 3000)

Now, if I assign sym column to variable ab

ab:`sym

Then, running below query is not giving proper output 

select [ab],px from t where [ab]=`IBM / returns empty table
?[t;(=;`sym;`IBM);0b; [ab]`px![ab]`px]/ type

Got understanding here and here but could not create a working query.

Solution

  • The answer above is close but there are some things to consider. The query you are running is basically:

    q)parse"select sym,px from t where sym=`IBM"
?
`t
,,(=;`sym;,`IBM)
0b
`sym`px!`sym`px

    The key thing here is that , usually indicates that a term needs enlisted. Additionally for the dictionary of column names you just need to join the value ab to px. With all that in mind I have modified your query above:

    q)?[t;enlist(=;`sym;enlist`IBM);0b;(ab,`px)!ab,`px]
sym px
------
IBM 20

    And assuming the where clause should also refer to ab:

    q)?[t;enlist(=;ab;enlist`IBM);0b;(ab,`px)!ab,`px]
sym px
------
IBM 20