SQL query to Functional query[Parse Tree]

Community Support
,
1

https://learninghub.kx.com/forums/topic/sql-query-to-functional-queryparse-tree

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

2

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

3

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

4

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

5

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.

6

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

Richard