select with assignment

[powerpeanuts]

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?

[eohara]

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 · Accepted solution]

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]

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