CODEWITHSUNDEEP
kdb+
ouali youcef
ouali youcef

Reputation: 41

Select column that begins with underscore in KDB

I have a KDB table stored in a server, where some columns begins with underscore (ex: _columnName). When trying to select such columns I get the following error (An error occurred during execution of the query. The server sent the response : _ ).

What would be the best way to get around this error ?

Thanks

Upvotes: 1

Views: 389

Answers (3)

Matt Moore
Matt Moore

Reputation: 2800

I would of thought .Q.id(sanitize) might of achieved Kyle's solution but looks like it doesn't. Could expand on Kyles to do the xcol programmatically.

sanitize:{(`$ .[string cols t;(::;0);{$["_" ~ x;"u";x]}]) xcol t}

sanitize[t]
ua ub c
-------
0  0  0
1  1  1
2  2  2

Upvotes: 0

kylebonnes
kylebonnes

Reputation: 936

If you really want/need to use qSQL, you can instead query a version of the table with renamed columns, using xcol:

q)tbl:flip(`$"_a";`$"_b";`c)!3 3#til 3
q)select ua from xcol[(`$("_a";"_b"))!`ua`ub;tbl]
ua
--
0
1
2

Then in all of your queries to the server, you would replace the table name with xcol[(`$("_a";"_b"))!`ua`ub;tbl]

Note: if you're querying the server using the common h"<query>" style, you'll need to escape the speech marks, i.e. xcol[(`$(\"_a\";\"_b\"))!`ua`ub;tbl]

Upvotes: 1

Maurice Lim
Maurice Lim

Reputation: 883

You can try using a functional select like the following:

q)tbl:flip(`$"_a";`$"_b";`c)!(til 3;til 3;til 3)
q)parse"select a from tbl"
?
`tbl
()
0b
(,`a)!,`a
q)?[`tbl;();0b;(enlist`$"_a")!enlist`$"_a"]
_a
--
0
1
2

Upvotes: 3

Related Questions