Query a column using the output from another column

Asked

Viewed 42 times

0

Suppose I make the following select:

select "nome", 
   "quantidade", 
   (quantidade * 2 - 5 % 3) AS numero
   ((quantidade * 2 - 5 % 3) * 5) AS segundo_numero
from "tabela"

How do I use the field numero (Which already has part of the formula solved) instead of rewriting it?

I know I can do it by performing an understatement:

SELECT aux.nome, aux.quantidade, aux.numero, (aux.numero * 5) AS segundo_numero
FROM
    (SELECT "nome", 
       "quantidade", 
       (quantidade * 2 - 5 % 3) AS numero
    FROM "tabela") AS aux

Has some more elegant way?

sql

  • that I know there is no way to do this without being a sub query, if your data * 2 - 5 % 3 are fixed an idea would already insert the value at the time of the Insert in a column.

  • 1

    depending on the database and formula, a computed/ calculated column can be created

  • You could do the calculation in the application instead of SELECT, since you will not use the values on the server side of the database, you should also remember to inform which database you are using, because each one has its own peculiarities

1 answer

2

If the database you are using supports CTE (Common Table Expression), I believe it is an interesting option because it facilitates the visualization of the SQL statement:

with aux as
(
  SELECT "nome", 
       "quantidade", 
       (quantidade * 2 - 5 % 3) AS numero
  FROM "tabela"
)

SELECT aux.nome, aux.quantidade, aux.numero, (aux.numero * 5) AS segundo_numero
FROM aux

I hope it will be useful

  • That doesn’t give the same result ?

  • 1

    yes, but I believe it facilitates future maintenance

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.