How to retrieve date from table in KDB as value

Question

I retrieve date from the table: my_date:select max date from table

now I want to use my_date this way: select from some_other_table where date within(my_date;now) but my_date has type table - how I may extract date as a value to use in the last query?

Answer 1

You should use 'exec'. 'exec' returns data in various forms like single value,list,dictionary etc., depending upon how it is being used.

     q) my_date:exec max date from table

This will return max date as a value.

But below call:

     q) exec date from table

This will reurn a list which will have all date values from table.

Answer 2

exec extracts a column as a vector. For your example you can bring the max aggregrator out to make it an atom that'll work with within like so:

select from some_other_table where date within(max exec date from table;now)

Similarly, if in memory you can use a table like a dictionary

max table`date

Answer 3

Two Scnario: 1. If you are querying a simple table, direct indexing should be fastest

select from tb2 where date within (max tb1`date;.z.D)

2. If you are querying to a splayed/partition table, you cannot use indexing so you need to do a indirect exec instead :

select from tb2 where date within (max exec from select date from tb1;.z.D)