Query results in 'Syntax error at end of input' (java / Postgres)

Question

I receive an error which I don't understand. This is the part of the query in the WHERE clause which results in an error.

WHERE 
CASE so.console_role
WHEN 'NONE' THEN so.orderid = '512' 
ELSE so.console_id IN (select console_id from service_order where so.orderid 
= '512')

Error:

ERROR: syntax error at end of input LINE 118:

Have someone an idea? Thanks in advance.

A case expression is exactly that an expression that returns a value, it doesn't work like if. — HoneyBadger
– HoneyBadger, Commented Apr 12, 2018 at 12:37
It's generally better to use AND/OR constructions instead of case expressions in the WHERE clause. — jarlh
– jarlh, Commented Apr 12, 2018 at 12:38
You need an end statement with this notation. Just a typo... — AxelH
– AxelH, Commented Apr 12, 2018 at 12:39
Argh stupid! I forgot the END. Thanks for the quick reply guys. I will look at the and/or construction, thanks for the tip. — Maurice Jansen
– Maurice Jansen, Commented Apr 12, 2018 at 12:47

Gordon Linoff · Accepted Answer · 2018-04-12 23:25:33Z

2

Don't use case expressions in the where clause. Use boolean logic:

WHERE (so.console_role = 'NONE' AND so.orderid = '512'
      ) OR
      (so.console_role <> 'NONE' AND so.console_id IN (select console_id from service_order where so.orderid = '512')
      )

Note that this does not take NULL values into account for so_console_role, although that is trivial to add.

Also, I suspect the logic in the second part is not correct, and you intend:

WHERE (so.console_role = 'NONE' AND so.orderid = '512'
      ) OR
      (so.console_role <> 'NONE' AND so.console_id IN (select so2.console_id from service_order so2 where so2.orderid = '512')
      )

The where clause in your version of the subquery is referring to so in the outer query.

edited Apr 12, 2018 at 23:25

answered Apr 12, 2018 at 12:48

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Maurice Jansen Over a year ago

Thanks Gordon! Why is a 'case' expression not recommended?

Gordon Linoff Over a year ago

@MauriceJansen . . . A case expression pretty much blocks efforts by the optimizer. In addition, boolean logic is sufficient to express the logic. In some rare cases, you want a case expression to control the order of evaluation, but that is unusual.

Maurice Jansen Over a year ago

Clearly! Thanks again for the help and explanation.

Collectives™ on Stack Overflow

Query results in 'Syntax error at end of input' (java / Postgres)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related