KDB using function in where clause

Question

Is there a way in kdb to use functions in queries. assume I have a table with columns

`red`blue`green`yellow`white`purple

which contain either values 0 or 1. Instead of querying

select where ((red=1) or (green=1))

can I use a function like

isRG:{((select green from x)=1) or ((select red from x)=1)}

to filter a select? I can do

f:[select from t] 

and it returns a column with true and false, but I cant figure out how to do something along the lines of

select from t where f[select from t]

to get all entries where f(x) is true

Answer 1

Yes - although you don't need to pass the entire table into the function, just the relevant columns:

/ define a table
tbl:flip`red`blue`green`yellow`white`purple!(0N;6)#36?0b;
    red blue green yellow white purple
    ----------------------------------
    1   0    0     0      1     1     
    1   0    0     0      0     1     
    1   0    0     0      0     0     
    0   0    1     0      0     0     
    1   1    0     0      0     0     
    0   0    0     0      1     0     

/ define a function to work on 2 columns - this func simply does or between the columns 
f:{[column1;column2] column1|column2};

/ use function on 2 columns of table
select from tbl where f[red;green]
    red blue green yellow white purple
    ----------------------------------
    1   0    0     0      1     1     
    1   0    0     0      0     1     
    1   0    0     0      0     0     
    0   0    1     0      0     0     
    1   1    0     0      0     0     

The key principles to keep in mind when using a function in a select where clause is:

• Each column argument is passed in as a vector
• The value returned by the function must be a vector of booleans (or boolean-equivalent type eg. integers, as 0 is considered false) of the same length

Answer 2

It seems easier to work with a dictionary in this scenario; using flip on t below:

q)t
red blue green yellow white purple
----------------------------------
0   1    0     1      1     0
q)
q)(key[x:flip[t]] where (raze value x=1))#x
blue  | 1
yellow| 1
white | 1

enlist it if you want the result as a table:

q)enlist (key[x:flip[t]] where (raze value x=1))#x
blue yellow white
-----------------
1    1      1

Another possibility might be to use a functional select and filter the list of column names to those where all values are equal to 1.