select with assignment

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?

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

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

 

Or you can think in vectors:

 

q)show t:flipab!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 tab 2 1.555556 1.384615

 

Breaking that down:

 

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