Yes I understand that I can create a keyed table, but I’m trying to understand why the type error and what sorts of queries will work on a rct. select works OK on a dictionary, regular crosstab (blank column on left), and keyed table. In constructing dictionaries where the arguments can be either a list or a conformable table there are four combinations: LL, LT, TL, TT. I’m getting a `type exception with the rct…does anyone have a select or exec statement that works w/o changing the dictionary? Thanks!
exec is really a general form of select and can produce tables, dictionaries and lists.
When you don’t supply a column name, the resulting aggregation will produce a dictionary, note that the aggregation clause is specifying just an aggregation
q)parse "exec sum c3 by c1,c2 from a"?a()c1c2!c1c2,(sum;c3)
When a column name is supplied, the result is promoted into a table. The aggregation clause here is a dictionary
q)parse "exec sumc3:sum c3 by c1,c2 from a"?a()c1c2!c1c2(,sumc3)!,(sum;c3)`
You can use .Q.qt to determine if the result is a table or keyed tab
Thanks Callum. I understand that I can create a table with the exec template by naming the aggregate field but my question is why the type exception with the rct? And is there any way with the rct the way it is to get an answer rather than a type exception with an exec or select query?
Because rct isn’t a table, it’s a dictionary. In your latter example you still haven’t produced a table, .Q.qt is false, and if you try to select a column from that table it will give you a type error again
In regards to tables, the following qsql query makes no sense
q)select xx from ct
c2 c3
xx
a b a 60
xx is not a column, which the select statement should have extracted, instead, you got a row from your dictionary.
In fact for both ct and rct if you examine with -3! you can see that they are not tables
A keyed table isn’t really a table either, it’s a dictionary too. The fact is that the select and exec templates work on dictionaries as well and keys are allowed in the select clause. When applied to a dictionary (the various types) the by clause is disallowed.
q)select finches,nuthatches from ct
| counter sightings
------- | ------------------
finches | ab 27
nuthatches| abcd 42
Oddly this seemingly formally correct syntax doesn’t work on a proper keyed table which *is* a dictionary:
q)kt2
family
counter sightings
finches
ab 27
nuthatches
abcd 42
sparrows
ab`c 50
woodpeckers
ab`c 153
q)exec finches from kt2
'finches
[0] exec finches from kt2
^
q)exec finches from kt2 finches
q)select from kt2 where family=`finches
family
counter sightings
finches
a b 27
Of course we still have the enigma of the rct (right crosstab) and the 'type exception. Is anyone able to write a select or exec template that works on that one? :)
A keyed table is a dictionary where the key and values are both tables, not just any dictionary. In your case, you have two different cases. In one the key is a list, in the other, the values are a list.
Your latter examples are demonstrating expected behaviour from https://code.kx.com/q/ref/qsql/#select. I’m unsure as to why you are expecting to extract rows with your column specification. In your case, it appears to just be indexing into the dictionary structure you have provided. To the best of my knowledge, exec and select for dictionaries is undocumented, so it’s not a surprise that the results are unpredictable.