cancel
Showing results for 
Search instead for 
Did you mean: 

select with assignment

powerpeanuts
New Contributor III

I remember in q, we can assign a temporary variable during select and use it again in the same column, but seems it is not working.

For example,

select (total+5)%(total:a+b) from t

this should be same as 

select (a+b+5)%(a+b) from t

 

What is wrong with the above?

1 ACCEPTED SOLUTION

rocuinneagain
Contributor III
Contributor III

You cannot do inline temporary variable creation as @eohara pointed out.

 

Instead you can use a lambda to avoid performing the calculation twice:

q)select {[a;b] (total+5)%(total:a+b)}[a;b] from t
b
--------
2
1.714286
1.555556

 

View solution in original post

3 REPLIES 3

eohara
New Contributor III
New Contributor III

9. Queries – q-sql - Q for Mortals (kx.com)

"Unlike in SQL, columns in the Select phrase do not actually exist until the final result table is returned. Thus a computed column cannot be used in other column expressions."

You'll need two different select statements here, e.g.

select (total+5)%total from select total:a+b from t

rocuinneagain
Contributor III
Contributor III

You cannot do inline temporary variable creation as @eohara pointed out.

 

Instead you can use a lambda to avoid performing the calculation twice:

q)select {[a;b] (total+5)%(total:a+b)}[a;b] from t
b
--------
2
1.714286
1.555556

 

SJT
Contributor III
Contributor III

Or you can think in vectors:

 

q)show t:flip`a`b!flip 3 2#2 3 4 5 6 7
a b
---
2 3
4 5
6 7

q)select (a+b+5)%(a+b) from t
a
--------
2
1.555556
1.384615

q)(%) . 5 0+\:sum t`a`b
2 1.555556 1.384615

 

Breaking that down:

 

q)t`a`b  / two cols, returned as a 2-row matrix
2 4 6
3 5 7
q)sum t`a`b  / a + b
5 9 13
q)5 0+\:sum t`a`b  / (a+b) with and without 5 added
10 14 18
5  9  13
q)(%) . 5 0+\:sum t`a`b  / their ratios
2 1.555556 1.384615