CODEWITHSUNDEEP
sqlpostgresql
sql
sql

Reputation: 3

SQL return column based on other column

i have 

SELECT col, col1 from table
CASE
WHEN other_col = 'Canada' THEN  1
    ELSE 2
END AS col2

it works fine, but i want to calculate

col3 based on value from col2

it should be simple col3 = value from col2 / 2 and return it in the same select

How to achieve it?

Upvotes: 0

Views: 42

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can use a lateral join in Postgres:

SELECT t.col, t.col1, v.col2
FROM table t CROSS JOIN LATERAL
     (VALUES (CASE WHEN other_col = 'Canada' THEN  1 ELSE 2 END)
     ) v(col2);

You can then use v.col2 to your heart's delight in any other expression you want to.

Upvotes: 0

SQLRaptor
SQLRaptor

Reputation: 681

The reason you can't use the alias is because of the 'all at once' expression evaluation. Expressions are not evaluated in order, but 'all at once' which also allows you to do things like 

UPDATE Table SET Col1 = Col2, Col2 = Col1...

Which would not be possible if the expressions were processed in order.

IMHO it's easier and clearer to use CTE instead of repeating the CASE.

;WITH CTE AS
(
SELECT col, col1, CASE
                  WHEN other_col = 'Canada' 
                    THEN  1
                  ELSE 2
                  END AS col2
FROM table
)
SELECT *, <Whatever Function On (Col2).
FROM CTE;

Upvotes: 0

forpas
forpas

Reputation: 164064

You can't use the column col2 in an expression like col2 / 2 inside the same select statement.
You can repeat the same CASE statement like this:

SELECT 
  col, 
  col1, 
  CASE WHEN other_col = 'Canada' THEN 1 ELSE 2 END AS col2
  CASE WHEN other_col  = 'Canada' THEN 0.5 ELSE 1 END AS col3
FROM table

or by nesting the SELECT statement inside another:

SELECT
  t.*,
  t.col2 / 2 AS col3
FROM (
  SELECT 
    col, 
    col1, 
    CASE WHEN other_col = 'Canada' THEN 1 ELSE 2 END AS col2
  FROM table 
) AS t

Upvotes: 1

Related Questions