KDB/q How to use qsql select statement to find item in list where type is empty list in a column of characters?

Question

I have a table and passed it into a function where I use the primary ID to generate a column of characters.

However, there are a few areas in this newly-created column where instead of characters it's an empty list: ()

When I check for type, the characters and empty lists are all listed as type 10h.

I'm trying to create a select statement that gets the primary IDs and this column for only those whose values are ().

I tried these, but they all gave me 'length errors:

select ID, Names from x where Sedol = ();
select ID, Names from x where Sedol = "()";
select ID, Names from x where type Sedol = 10h;
select ID, Names from x where type Sedol = 0h;

Would be curious to see if any of you have any insight into this? Is it possible to even select by type within a column?

Answer 1

Sample table:

t:([] ID:1 2 3 4;Sedol:("aa";"bb";();"cc"))
t
ID Sedol
--------
1  "aa"
2  "bb"
3  ()
4  "cc"

Search for 0h type:

select from t where 0h=type each Sedol
ID Sedol
--------
3

• each is used to test each row https://code.kx.com/q/ref/maps/#each

Search for exactly ():

select from t where Sedol~\:()
ID Sedol
--------
3

• ~ is match https://code.kx.com/q/ref/match/
• \: is each-left https://code.kx.com/q/ref/maps/#each-left-and-each-right

Answer 2

1. You can't use the = operator to compare a column to an empty list. It would attempt to compare each element in turn from the column with the corresponding element from the empty list, which doesn't exist, so you get a length error.

2. The same applies to this one, except here the right operand is a list of 2 elements, "(" and ")". This would actually work if the Sedol column was a character column and the table had only 2 rows, and it would compare the value in the first row to "(" and the one in the 2nd row to ")", which is still not what you want. In any other case you get a length error.

3. Keep in mind that the evaluation order is right to left. Therefore type Sedol = 10h is equivalent to type (Sedol = 10h). However even if parenthesizing correctly, type[Sedol]=10h will take the type of the entire column, which is most likely 0h, and compare that to 10h, which is false. What you actually want is to compare the type of each element to 10h, so 10h=type each Sedol. Note that I flipped around the arguments just to avoid the extra parentheses.

So to put it together:

select ID, Names from x where 0h=type each Sedol

or

select ID, Names from x where 10h=type each Sedol

Answer 3

Based on your comment about types it seems that these are not empty lists () but instead "".

When I check for type, the characters and empty lists are all listed as type 10h.

In either case you can check for a 0 count which indicates an empty list.

select ID, Names from x where 0=count'[Sedol]