select from with wildcard, in Q KDB

Question

I use KDB with a tool that let me set queries. With this tool, only the parameters (filter value) can be changed with user interaction, not the structure of the query. I need a query to be run on user action. By default, I want it to select every rows, and after he select something, I want the query to be filtered by his selection.

For example: By default: select from quote where symbol = *

After the user chooses a symbol: select from quote where symbol = `AAPL

However, the default example doesn't work, because there is no * wildcard in KDB, unlike SQL. How can I get all rows by default then?

score 3 · Accepted Answer · answered Sep 20 '16 at 09:05

3

Not sure exactly what you're trying to achieve here or what your exact constraints are.

If you're saying that the equals (=) is fixed then you're restricted to selecting one symbol only....yet you want to return all rows? Technically this can be done in a select statement like so

select from table where symbol=symbol

but this may not work if your "tool" is expecting an input of type symbol.

To use a wildcard you'd need "like" instead of equals but it sounds like you can't control that.

Have you considered that this tool is designed to only allow one symbol filter for a reason ? Perhaps returning all rows would be too much data, perhaps it would be too slow, perhaps it would take too much memory. Feels like you're trying to hack a shortcut

answered Sep 20 '16 at 09:05

terrylynch

11,844
13
21

Thank you for your answer. I can control "like" instead of equals. What would be the wildcard then ? – Sithered Sep 21 '16 at 13:58
select from t where symbol like "?*" – terrylynch Sep 21 '16 at 14:40
Actually, that won't return null symbols, this would: select from t where symbol like enlist "*" – terrylynch Sep 21 '16 at 14:45

score 1 · Answer 2 · answered Sep 21 '16 at 02:03

You can cast the column in question to a string and use like.

A way to achieve your requirement is to either select for all syms (if the user doesn't specify a filter or select for a subset which the user defines.) You might implement this like;

q)show myTable:update stamps:dates+'times from ([]syms:`AAPL`GOOG`PRDC`LALA`LOLO;times:5?.z.T;dates:5?.z.D)
syms times        dates      stamps
----------------------------------------------------------
AAPL 00:48:15.333 2003.04.10 2003.04.10D00:48:15.333000000
GOOG 00:39:29.270 2015.06.14 2015.06.14D00:39:29.270000000
PRDC 00:15:49.627 2016.05.21 2016.05.21D00:15:49.627000000
LALA 01:30:32.099 2015.01.04 2015.01.04D01:30:32.099000000
LOLO 00:31:19.910 2013.12.01 2013.12.01D00:31:19.910000000
q)filter1:{[allSyms;usrSyms] symList:$[all null usrSyms;allSyms;usrSyms]; select from myTable where syms in symList}[exec distinct syms from myTable;]
/filter1 is a projection so you don't have to query the table for all syms each time
q)filter1`GOOG`APPL
syms times        dates      stamps
----------------------------------------------------------
GOOG 00:39:29.270 2015.06.14 2015.06.14D00:39:29.270000000
q)filter1`GOOG`AAPL
syms times        dates      stamps
----------------------------------------------------------
AAPL 00:48:15.333 2003.04.10 2003.04.10D00:48:15.333000000
GOOG 00:39:29.270 2015.06.14 2015.06.14D00:39:29.270000000
q)filter1`
syms times        dates      stamps
----------------------------------------------------------
AAPL 00:48:15.333 2003.04.10 2003.04.10D00:48:15.333000000
GOOG 00:39:29.270 2015.06.14 2015.06.14D00:39:29.270000000
PRDC 00:15:49.627 2016.05.21 2016.05.21D00:15:49.627000000
LALA 01:30:32.099 2015.01.04 2015.01.04D01:30:32.099000000
LOLO 00:31:19.910 2013.12.01 2013.12.01D00:31:19.910000000
q)

I'd prefer to implement something like;

q)filter2:{[usrSyms] ?[myTable;$[all null usrSyms;();enlist(in;`syms;enlist usrSyms)];0b;()]}
q)filter2`
syms times        dates      stamps
----------------------------------------------------------
AAPL 00:48:15.333 2003.04.10 2003.04.10D00:48:15.333000000
GOOG 00:39:29.270 2015.06.14 2015.06.14D00:39:29.270000000
PRDC 00:15:49.627 2016.05.21 2016.05.21D00:15:49.627000000
LALA 01:30:32.099 2015.01.04 2015.01.04D01:30:32.099000000
LOLO 00:31:19.910 2013.12.01 2013.12.01D00:31:19.910000000
q)filter2`GOOG
syms times        dates      stamps
----------------------------------------------------------
GOOG 00:39:29.270 2015.06.14 2015.06.14D00:39:29.270000000
q)filter2`GOOG`AAPL
syms times        dates      stamps
----------------------------------------------------------
AAPL 00:48:15.333 2003.04.10 2003.04.10D00:48:15.333000000
GOOG 00:39:29.270 2015.06.14 2015.06.14D00:39:29.270000000
q)

select from with wildcard, in Q KDB

2 Answers2