SQL Error - Column does not exist (in SELECT as)

Question

I am joining two tables: breeds + breed_characteristics (bc)

But I'm getting the following error:

PG::UndefinedColumn: ERROR: column "val" does not exist LINE 11

I'm not sure what's wrong, here is my SQL:

SELECT                                                                      
  breeds.*,                                                                 
  CASE bc.user_val                                                          
    WHEN NULL THEN bc.value                                                   
     ELSE (bc.value + (bc.user_val/2))/2                                       
   END AS val                                                                
  FROM                                                                        
  breed_characteristics bc       
  INNER JOIN breeds ON breeds.id = bc.breed_id                                
  WHERE bc.characteristic_id = 45                               
  AND val BETWEEN 4 AND 5                              
  ORDER BY val DESC

(Executing this query on Postgres through Active Record)

Answer 1

You can't use expression alias val in where clause like that.

It's because there is an order in which SQL is executed specified in the SQL standard. Here, the WHERE clause is evaluated before SELECT and hence, the WHERE clause is not aware of the alias you created in the SELECT. The ORDER BY comes after the SELECT and hence can utilize aliases.

Just replace the alias with the actual case expression like this:

SELECT                                                                      
  breeds.*,                                                                 
  CASE bc.user_val                                                          
    WHEN NULL THEN bc.value                                                   
     ELSE (bc.value + (bc.user_val/2))/2                                       
   END AS val                                                                
  FROM                                                                        
  breed_characteristics bc       
  INNER JOIN breeds ON breeds.id = bc.breed_id                                
  WHERE bc.characteristic_id = 45                               
  AND CASE WHEN bc.user_val is NULL THEN bc.value                                                   
     ELSE (bc.value + (bc.user_val/2))/2                                       
   END BETWEEN 4 AND 5                              
  ORDER BY val DESC

However, you can use alias in order by clause.

Answer 2

One option to avoid restating the CASE expression in multiple places is to use a subquery:

SELECT *
FROM
(
    SELECT b.*,
           bc.characteristic_id,
           CASE WHEN bc.user_val IS NULL THEN bc.value
                ELSE (bc.value + (bc.user_val / 2)) / 2                                       
           END AS val                            
    FROM breed_characteristics bc       
    INNER JOIN breeds b
        ON breeds.id = bc.breed_id
) t
WHERE t.characteristic_id = 45 AND
      t.val BETWEEN 4 AND 5                              
ORDER BY t.val DESC