SQL query to Functional query[Parse Tree]

Community Support
1

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

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

3

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:table1table2table2!datafielddatafield2datafield3 {![`dictionary;();ob;(enlist `coldic)!enlist($;“p”;`coldic)] 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

4

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

 

q)t1:(c1:abc;c2:1 2 3;c3:("10:00";"10:30";"11:00")) q)t2:([]c1:def;c2:4 5 6;c4:(“09:00”;“10:30”;“11:30”)) q)d:t1t2!(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;c3c4] 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

5

You could use a dot amend

dictionary:t1t2t3!(([]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