cancel
Showing results for 
Search instead for 
Did you mean: 

SQL query to Functional query[Parse Tree]

racuna00
New Contributor II

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

1 ACCEPTED SOLUTION

dcrossey
New Contributor III
New Contributor III

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

View solution in original post

5 REPLIES 5

dcrossey
New Contributor III
New Contributor III

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
New Contributor II

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
New Contributor III
New Contributor III

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

View solution in original post

racuna00
New Contributor II

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

Richard

TerryLynch
New Contributor II

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.