CTE's can give you the dose of syntactic sugar you're craving:
[No, you can reference other column definitions within the same SELECT
statement.]
WITH w_age_col AS (SELECT *,CAST((julianday('now')-julianday(dob))/365.25
AS INT)age FROM the_table)
SELECT *, age,87-age life_expectancy FROM w_age_col;
On Thu, Nov 23, 2017 at 2:17 PM, Tony Papadimitriou <to...@acm.org> wrote:
> I don’t know if this has come up before.
>
> Example:
>
> select cast((julianday('now')-julianday(dob))/365.25 as int) age,
> 87-cast((julianday('now')-julianday(dob))/365.25 as int)
> life_expectancy
>
> (... assuming 87 year average life span)
>
> This works, but as you see the age calculation has to be repeated in every
> column that needs it (and there could many more).
>
> Why not be able to this instead?
>
> select cast((julianday('now')-julianday(dob))/365.25 as int) age,
> 87-age life_expectancy
>
> Note that age is defined before it is referenced.
>
> Apparently, ‘age’ can be used in a subsequent join but not a subsequent
> column definition.
> Is there a technical limitation for this or simply an unimplemented
> feature?
>
> BTW, is ‘now’ value locked during the query execution to avoid the
> possibility (however small) of two columns ending up with different age
> calculations (e.g., running during date crossover on someone’s birthday)?
>
> Thanks.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
