Can someone please post functional form of q-sql like select insert update exec. any small example would do
http://code.kx.com/wiki/JB:QforMortals2/queries\_q\_sql#Functional\_select
http://code.kx.com/wiki/JB:QforMortals2/queries\_q\_sql#Functional\_update
http://code.kx.com/wiki/JB:QforMortals2/queries\_q\_sql#Functional\_delete
http://code.kx.com/wiki/JB:QforMortals2/queries\_q\_sql#Functional\_exec
http://code.kx.com/wiki/Reference/insert
Thanks,
Sean
/define a table
q)t:flip datetimesymprice!“DTSF”$:()
q)t
date time sym price
q)
/1 row insert
q)t insert(.z.d;.z.t;a;10.0)
,0
q)t
date time sym price
2015.07.06 22:31:25.170 a 10
/multiple insert
q)t insert(5#.z.d;5?.z.t;abcde;5?10.0)
1 2 3 4 5
q)t
date time sym price
2015.07.06 22:31:25.170 a 10
2015.07.06 04:30:47.898 a 3.927524
2015.07.06 04:07:38.923 b 5.170911
2015.07.06 06:22:45.093 c 5.159796
2015.07.06 19:06:46.591 d 4.066642
2015.07.06 10:07:47.382 e 1.780839
/q sql select statement
q)select date,time,price from t where price>5
date time price
2015.07.06 22:31:25.170 10
2015.07.06 04:07:38.923 5.170911
2015.07.06 06:22:45.093 5.159796
q)
/parse the select statement to view the functional form
q)parse “select date,time,price from t where price>5”
?
`t
,(>;`price;5)
0b
datetimeprice!datetimeprice
q)
/from the result above the order is
/?[table_name;where clause;by clause;columns to be returned]
/note in the parse statement the commas get replaced with an ‘enlist’ as shown
/where clause must be a list, by clause and aggregate clause are dictionaries
q)?[t;enlist(>;`price;5);0b;`date`time`price!`date`time`price]
date time price
2015.07.06 22:31:25.170 10
2015.07.06 04:07:38.923 5.170911
2015.07.06 06:22:45.093 5.159796
“/where clause must be a list, by clause and aggregate clause are dictionaries”
This is for a select statement, not all q sql statements.
q)exec price from t
10 3.927524 5.170911 5.159796 4.066642 1.780839
q)parse “exec price from t”
?
`t
()
()
,`price
q)
q)
q)?[t;();();enlist `price]
`price
q)?[t;();();`price]
10 3.927524 5.170911 5.159796 4.066642 1.780839
Varies slightly for exec, delete but the parse logic produces the functional form and it can be worked out from there.
Also another note, there are 2 extra fields which can be applied if needs be.
/as before
q)?[t;enlist(>;`price;5);0b;`date`time`price!`date`time`price]
date time price
2015.07.06 22:31:25.170 10
2015.07.06 04:07:38.923 5.170911
2015.07.06 06:22:45.093 5.159796
q)
/start at row index 1 and take 1 row of the result
q)?[t;enlist(>;`price;5);0b;`date`time`price!`date`time`price;1 1]
date time price
2015.07.06 04:07:38.923 5.170911
q)
/take the first 2 rows of the result
q)?[t;enlist(>;`price;5);0b;`date`time`price!`date`time`price;2]
date time price
2015.07.06 22:31:25.170 10
2015.07.06 04:07:38.923 5.170911
q)
/ascend the table on price and take the first 2 rows
q)?[t;enlist(>;price;5);0b;datetimeprice!datetimeprice;2;(iasc;price)]
date time price
2015.07.06 06:22:45.093 5.159796
2015.07.06 04:07:38.923 5.170911
Hope this helps,
Marcus