• kdb+

  SQL query to Functional query[Parse Tree]

  Posted by Laura on September 23, 2021 at 12:00 am

  Hi Community,

  I have a dictionary of tables, so I want to convert them into functional queries but when I try to build it, it retrieves a type issue, Do you know if it is possible? Attached pic!

  Thanks,

  Richard

  Laura replied 8 months ago 3 Members · 5 Replies
• 5 Replies

• 

  dcrossey

  Member

  September 24, 2021 at 12:00 am

  You could update the type of each time column for each table in your dictionary as follows:

   

  q)t1:([]c1:`a`b`c;c2:1 2 3;c3:("10:00";"10:30";"11:00")) 
  q)t2:([]c1:`d`e`f;c2:4 5 6;c4:("09:00";"10:30";"11:30")) 
  q)d:`t1`t2!(t1;t2) 
  q)d[`t1] 
  c1 c2 c3 
  ------------- 
  a 1 "10:00" 
  b 2 "10:30" 
  c 3 "11:00" 
  
  q)d2:{![x;();0b;enlist[y]!enlist ($;"T";y)]}'[d;`c3`c4] 
  q)d2[`t1] 
  c1 c2 c3 
  ------------------ 
  a 1 10:00:00.000 
  b 2 10:30:00.000 
  c 3 11:00:00.000

   

  This is an example of using binary each (also known as each both), passing in one key/value of the dictionary and one column per iteration into the lambda.

  Note – I’ve used time only “T” as a brief example here. I noticed you were using lower case “p” – if your time/date column is a string column, you’ll need to use upper case “P” to cast correctly.

  Cheers,

  David

• 

  dcrossey

  Member

  September 24, 2021 at 12:00 am

  Hi Richard,

  Instead of using the list structure, try simply using the dictionary with the key as the first clause e.g.

  q)t1:([]c1:`a`b`c;c2:1 2 3); 
  q)t2:([]c1:`d`e`f;c2:4 5 6); 
  q)d:`t1`t2!(t1;t2); 
  q)d 
  t1| +`c1`c2!(`a`b`c;1 2 3) 
  t2| +`c1`c2!(`d`e`f;4 5 6)  
  q)?[d[`t1];();0b;()]; 
  c1 c2 
  ----- 
  a  1 
  b  2 
  c  3

  Note – If you are purely selecting from the dictionary, you don’t need to use functional form at all. Simply pass the table name key into your dictionary:

  q)d[`t1] 
  c1 c2 
  ----- 
  a  1 
  b  2 
  c  3  
  q)d[`t2] 
  c1 c2 
  ----- 
  d  4 
  e  5 
  f  6

  Hope this helps,

  David

• 

  Laura

  Administrator

  September 24, 2021 at 12:00 am

  Hi David,

  Thanks for your kindly reply. I want to use functional query, because I have in each table a field(time-date) to be converted into timestamp. So, instead of using something like this:

  update "p"$datefield from dictionary[`table1] 
  update "p"$datefield2 from dictionary[`table2] 
  update "p"$datefield3 from dictionary[`table3]

  I wanted to use this:

  coldic:`table1`table2`table2!`datafield`datafield2`datafield3 {![`dictionary[x];();ob;(enlist `coldic[x])!enlist($;"p";`coldic[x])] x} each key coldic

  But it is not updating the tables, this is the output :

  “” “” “” “” “” “” “” “” “” “” “” “” “” “” “” “” “” “” “” “” “” “” ..

  I’m sorry for not being clear in my concern,

  Thanks,

  Richard

• 

  terrylynch

  Member

  September 24, 2021 at 12:00 am

  You could use a dot amend

  dictionary:`t1`t2`t3!(([]datefield:2?0Wj);([]datefield2:2?0Wj);([]datefield3:2?0Wj)); 
  .[`dictionary;;"p"$]each((`t1;`datefield);(`t2;`datefield2);(`t3;`datefield3));

  Assumes your tables are unkeyed.

• 

  Laura

  Administrator

  September 24, 2021 at 12:00 am

  Thank you David! This is definitely what I was looking for!!Great!!

  Richard

Log In to Reply