CODEWITHSUNDEEP
javasqlpostgresql
Maurice Jansen
Maurice Jansen

Reputation: 81

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

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.

Upvotes: 0

Views: 2444

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270331

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.

Upvotes: 2

Related Questions