Question on functional SQL

Community Support
1

I met problem when constructing a simple functional SQL statement. 

Say the table have a row of symbols of string values, and I want to filter out orders with wanted symbols. The q-sql command works fine, but the functional sql command returned type error.

q)t:([]orderid:111222333444;symbol:("aa.b";"cc.d";"ee.f";"gg.h"))q)wanted:("aa.b";"cc.d")q)select orderid,symbol from t where any symbol like/: wantedorderid symbol--------------111 "aa.b"222 "cc.d"q)c:enlist (any;((like/:);symbol;wanted))q)?[t;c;0b;()]'type [0] ?[t;c;0b;()] ^`

Can anyone help to point out what’s the problem with the functional sql statement ?

2

You need to place the adverb before the like 

q)c:enlist (any;((/:;like);symbol;wanted))

To assist in building up a functional statement, you can just parse the original q-sql statement

q)parse "select orderid,symbol from t where any symbol like/: wanted"?t,(max$[“b”];((/:;like);symbol;wanted))0borderidsymbol!orderidsymbol`

Then you just need to read the k) syntax, single , is an enlist, max$[“b”] is any. There is a useful whitepaper on all of this I’d recommend https://code.kx.com/q/wp/parse_trees_and_functional_forms.pdf 

3

Hi Lawrence

This is an easy fix. You’re just missing a backtick before wanted 

q)c:enlist (any;((like/:);symbol;wanted))

q)?[t;c;0b;()]

orderid symbol

111     “aa.b”
222     “cc.d”

Regards,

Cathal

On Wednesday, 2 January 2019 12:37:39 UTC, Lawrence Li wrote:

I met problem when constructing a simple functional SQL statement. 

Say the table have a row of symbols of string values, and I want to filter out orders with wanted symbols. The q-sql command works fine, but the functional sql command returned type error.

q)t:([]orderid:111222333444;symbol:("aa.b";"cc.d";"ee.f";"gg.h"))q)wanted:("aa.b";"cc.d")q)select orderid,symbol from t where any symbol like/: wantedorderid symbol--------------111 "aa.b"222 "cc.d"q)c:enlist (any;((like/:);symbol;wanted))q)?[t;c;0b;()]'type [0] ?[t;c;0b;()] ^`

Can anyone help to point out what’s the problem with the functional sql statement ?

4

Thank you Callum. That paper is very helpful.

5

A trick to find out how to properly construct your functional queries: parse