'case column' in HAVING clause

Philia

Hello,

I'm having problem migrating to postgreSQL from MySQL, some of my queries does not work in postgres.

Query(simplified):

Expand|Select|Wrap|Line Numbers

 SELECT t1.bus_stop, t1.time,

(case when t1.time >2200 then t1.time + 10000 else t1.time END) AS sort_column

FROM routes AS t1

GROUP BY  t1.bus_stop, t1.time

HAVING sort_column > 2200 ORDER BY sort_column  LIMIT 1

Error:

Expand|Select|Wrap|Line Numbers

ERROR: column "sort_column" does not exist

How to use a 'case column' in HAVING clause?

rski

Maybe like this

Expand|Select|Wrap|Line Numbers

 
SELECT i,j, sort_kolumn from (

SELECT t1.bus_stop as i, t1.time as j,

(case when t1.time >2200 then t1.time + 10000 else t1.time END) AS sort_column FROM routes AS t1) foo

GROUP BY  i,j

HAVING sort_column > 2200 ORDER BY sort_column  LIMIT 1

Does it work?

Philia

Quote:

Originally Posted by rski

Maybe like this

Expand|Select|Wrap|Line Numbers

 
SELECT i,j, sort_kolumn from (

SELECT t1.bus_stop as i, t1.time as j,

(case when t1.time >2200 then t1.time + 10000 else t1.time END) AS sort_column FROM routes AS t1) foo

GROUP BY  i,j

HAVING sort_column > 2200 ORDER BY sort_column  LIMIT 1

Does it work?

Yes, it works. Thank you rski. :)
But for me it looks like way around the problem, is there a better way to do this?
I'm migrating to postgres to speed up my aplication so I'm concerned about the speed of this query.

rski

If i'm right HAVING clause can't have CASE clause.
Say what do you want to achieve and what is table structure, maybe there is another solution than yours.

'case column' in HAVING clause

re: 'case column' in HAVING clause

re: 'case column' in HAVING clause

re: 'case column' in HAVING clause

Similar PostgreSQL Database bytes

/bytes/development

/bytes/it

/bytes/about