Welcome to the new home of the KX Community and KX Academy! Existing users are required to reset their passwords to log in

KX Community

Find answers, ask questions, and connect with our KX Community around the world.
KX Community Guidelines

Home Forums kdb+ SQL query to Functional query[Parse Tree]

  • 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 1 year 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.

Start of Discussion
1 of 5 replies September 2021
Now