Derby SQL calculation

Question

I'm having a trouble to do a calculation in Derby.

The problem is the next:

Select column1, (column1 + 10) as newCol, 
(column1+newCol) as newCol2 from sometable;

This throws an error saying that newCol does not exist, in the case (column1+newCol).

Why is not that correct?

Thanks for your help!

You cannot use an alias in the same `select` where it is defined. — Gordon Linoff, Apr 27 '19 at 01:57
Thanks very much. So how can I perform what I want? I need to do something like that but dont know how. — Rafael Cabrera Ferreras, Apr 27 '19 at 01:58
I don't know if Derby supports common table expressions, but if it does: `with cteData as (select column1, column1 + 10 as newCol from sometable) select column1, newCol, column1 + newCol from cteData`. Or just bite the bullet and use `Select column1, (column1 + 10) as newCol, (column1+column1+10) as newCol2 from sometable` — Bob Jarvis - Слава Україні, Apr 27 '19 at 02:43

score 1 · Accepted Answer · answered Apr 27 '19 at 07:06

You can't access a column alias on the same level where you define it.

You need a derived table:

select column1, newcol, column1 + newcol as newcol2
from (
  Select column1, (column1 + 10) as newCol
  from sometable
) t;

1 Answers1