Referencing new column

[powerpeanuts]

For example:

t:([] name:`Dent`Beeblebrox`Prefect; iq:98 42 126)

select name, iq2:iq%100 from t where iq2>1

I would like to reference the newly created "iq2" column in the where clause, but the above command fails. How to modify it to make it work?

[davidcrossey · Accepted solution]

Hi powerpeanuts,

You would need to add your new column first, before trying to use it in the where clause. For example:

q)t:([] name:`Dent`Beeblebrox`Prefect; iq:98 42 126)
q)select name, iq2 from (update iq2:iq%100 from t) where iq2>1
name    iq2
------------
Prefect 1.26

Thanks for posting your question on the KX Community. Great username too!

Kind regards,

David

[cillianreilly]

As David mentioned, you can't reference iq2 before you create it. If your where clause isn't too wieldy, you can just include that in the qsql query to create column iq2:

q)select name,iq2:iq%100 from t where 1<iq%100
name    iq2 
------------
Prefect 1.26

This does the calculation twice, so be careful with particularly heavy where conditions.