loadTableBySQL

loadTableBySQL(sql)

sql is a metacode object representing a SQL query. It can use where clause to filter partitions or rows and use SELECT statement to select columns including calculated columns. However, it cannot use TOP clause, GROUP BY clause, ORDER BY clause, CONTEXT BY, and LIMIT.

Load only the rows of a partitioned table that satisfy the filtering conditions in a SQL query to memory. The result is a partitioned in-memory table with the same partitioning scheme as the database on disk.

n=1000000
t=table(rand('A'..'Z',n) as sym, 2000.01.01+rand(365,n) as date, 10.0+rand(2.0,n) as price1, 100.0+rand(20.0,n) as price2, rand(10,n) as qty1, rand(100,n) as qty2)

db = database("dfs://tradeDB", VALUE, 'A'..'Z')
trades=db.createPartitionedTable(t,`trades,`sym).append!(t)

sample=select * from loadTableBySQL(<select * from trades where date between 2000.03.01 : 2000.05.01>)
sample=select * from loadTableBySQL(<select sym, date, price1, qty1 from trades where date between 2000.03.01 : 2000.05.01>)

dates = 2000.01.16 2000.02.14 2000.08.01
st = sql(<select sym, date, price1, qty1>, trades, expr(<date>, in, dates))
sample = select * from loadTableBySQL(st)

colNames =`sym`date`qty2`price2
st= sql(sqlCol(colNames), trades)
sample = select * from loadTableBySQL(st)