CODEWITHSUNDEEP
kdb+
marrowgari
marrowgari

Reputation: 427

Parameterize select query in unary kdb function

I'd like to be able to select rows in batches from a very large keyed table being stored remotely on disk. As a toy example to test my function I set up the following tables t and nt...

t:([sym:110?`A`aa`Abc`B`bb`Bac];px:110?10f;id:1+til 110)
nt:0#t

I select from the table only records that begin with the character "A", count the number of characters, divide the count by the number of rows I would like to fetch for each function call (10), and round that up to the nearest whole number...

aRec:select from t where sym like "A*"
counter:count aRec
divy:counter%10
divyUP:ceiling divy

Next I set an idx variable to 0 and write an if statement as the parameterized function. This checks if idx equals divyUP. If not, then it should select the first 10 rows of aRec, upsert those to the nt table, increment the function argument, x, by 10, and increment the idx variable by 1. Once the idx variable and divyUP are equal it should exit the function...

idx:0
batches:{[x]if[not idx=divyUP;batch::select[x 10]from aRec;`nt upsert batch;x+:10;idx+::1]}

However when I call the function it returns a type error...

q)batches 0
'type
[1]  batches:{[x]if[not idx=divyUP;batch::select[x 10]from aRec;`nt upsert batch;x+:10;idx+::1]}
                                                 ^

I've tried using it with sublist too, though I get the same result...

batches:{[x]if[not idx=divyUP;batch::x 10 sublist aRec;`nt upsert batch;x+:10;idx+::1]}

q)batches 0
'type
[1]  batches:{[x]if[not idx=divyUP;batch::x 10 sublist aRec;`nt upsert batch;x+:10;idx+::1]}
                                          ^

However issuing either of those above commands outside of the function both return the expected results...

q)select[0 10] from aRec
sym| px        id
---| ------------
A  | 4.236121  1
A  | 5.932252  3
Abc| 5.473628  5
A  | 0.7014928 7
Abc| 3.503483  8
A  | 8.254616  9
Abc| 4.328712  10
A  | 5.435053  19
A  | 1.014108  22
A  | 1.492811  25

q)0 10 sublist aRec
sym| px        id
---| ------------
A  | 4.236121  1
A  | 5.932252  3
Abc| 5.473628  5
A  | 0.7014928 7
Abc| 3.503483  8
A  | 8.254616  9
Abc| 4.328712  10
A  | 5.435053  19
A  | 1.014108  22
A  | 1.492811  25

Upvotes: 0

Views: 429

Answers (2)

Fiona Morgan
Fiona Morgan

Reputation: 520

You can't use the select[] form with variable input like that, instead you can use a functional select shown in https://code.kx.com/q4m3/9_Queries_q-sql/#912-functional-forms where you input as the 5th argument the rows you want

Hope this helps!

Upvotes: 1

Rahul
Rahul

Reputation: 3969

The issue is that in your example, select[] and sublist requires a list as an input but your input is not a list. Reason for that is when there is a variable in items(which will form a list), it is no longer considered as a simple list meaning blank(space) cannot be used to separate values. In this case, a semicolon is required. 

q) x:2
q) (1;x) / (1 2)

Select command: Change input to (x;10) to make it work.

q) t:([]id:1 2 3; v: 3 4 5)
q) {select[(x;2)] from t} 1

`id  `v
---------
 2    4
 3    5

Another alternative is to use 'i'(index) column:

q) {select from t where i within x + 0 2} 1

Sublist Command: Convert left input of the sublist function to a list (x;10).

 q) {(x;2) sublist t}1

Upvotes: 2

Related Questions