SQL query to Functional query[Parse Tree]

[racuna00]

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

[dcrossey]

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

[racuna00]

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

[dcrossey · Accepted solution]

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

[racuna00]

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

Richard

[TerryLynch]

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.