PostgreSQL column "enginePower" does not exist

Question

I get this error when I try to use my SELECT AS name.

CREATE VIEW CanDrive AS
    SELECT Car.name,
    (SELECT SUM(engineParts) FROM Engine WHERE Car.engine = Engine.type) AS enginePower,

    CASE WHEN enginePower >= 30 THEN 'yes' ELSE 'no' END AS canDrive

    FROM Car;

Why do I get the error column "enginePower" does not exist when I clearly stated it?

@muistooshort Do you know the problem? Since i got downvoted this post got no attention — mrfr, Dec 03 '17 at 20:59
You can’t reference other columns created at the same level. You could use a sub-query instead. — jcaron, Dec 03 '17 at 21:06
@jcaron So i have to copypaste my entire query again? `CASE WHEN (SELECT SUM(engineParts) FROM Engine WHERE Car.engine = Engine.type) >= 30 THEN 'yes' ELSE 'no' END AS canDrive` Isnt that the whole reason with select as? — mrfr, Dec 03 '17 at 21:22

score 2 · Answer 1 · answered Dec 03 '17 at 21:53

EnginePower is derived and doesn't mean anything yet within the context of that query -- it's just an alias. You could overcome this by evaluating the same expression as above:

   case
     when (SELECT SUM(engineParts) FROM Engine WHERE Car.engine = Engine.type) >= 30
       then 'yes'
     else 'no'
   end as canDrive

Or even better by wrapping it in a common table expression/subquery:

with cte as (
    SELECT
       Car.name,
      (SELECT SUM(engineParts) FROM Engine WHERE Car.engine = Engine.type) AS enginePower
    FROM Car
)
select name, enginePower, case when enginePower >= 30 then 'yes' else 'no' end as canDrive
from cte

Or better yet, perhaps, just convert your scalars into a standard join:

select
  c.name, sum (engineParts) as enginePower,
  case when sum (engineParts) >= 30 then 'yes' else 'no' end as hasPower
from
  car c
  left join engine e on
    c.engine = e.type
group by
  c.name

PostgreSQL column "enginePower" does not exist

1 Answers1