Use computed SELECT column in another computed SELECT column

user_16311828 0
Viewed 29

How can I make this work without repeating the definition of somaDiarias? For this query:

SELECT nome AS nomeHospede,
   valorDiaria*DATE_PART('day',dataSaida::timestamp - dataEntrada::timestamp) AS somaDiarias,
   CASE WHEN (somaDiarias >=0) AND (somaDiarias <= 1000) THEN 'E'
        WHEN (somaDiarias >=1000.01) AND (somaDiarias <= 3000.00) THEN 'D'
        WHEN (somaDiarias >=3000.01) AND (somaDiarias <= 7000.00) THEN 'C'
        WHEN (somaDiarias >=7001.00) AND (somaDiarias <= 10000) THEN 'b'
        WHEN (somaDiarias >10000) THEN '10'
        END AS Classe
    FROM hospedagem a
    JOIN hospede b on a.codHospede = b.codHospede

I get the following error:

ERROR:  column "somadiarias" does not exist
postgresql sql
1 Answers

You will have to use a CTE or subquery. Going the CTE route, and tighetening up the CASE logic, we can try:

WITH cte AS (
    SELECT valorDiaria, nome AS nomeHospede,
           valorDiaria*DATE_PART('day', dataSaida::timestamp - dataEntrada::timestamp) AS somaDiarias
    FROM hospedagem a
    INNER JOIN hospede b ON a.codHospede = b.codHospede
)

SELECT
    somaDiarias,
    nomeHospede,
    CASE WHEN somaDiarias <= 1000  THEN 'E'
         WHEN somaDiarias <= 3000  THEN 'D'
         WHEN somaDiarias <= 7000  THEN 'C'
         WHEN somaDiarias <= 10000 THEN 'b'
         ELSE '10' END AS Classe
FROM cte;
user_18632290
Related