Case with parameter in Interbase 2017

Question

Good evening guys, I always used Firebird, but I decided to try the 2017 Interbase for mobile apps. The following SQL works normally on Firebird and Sqlite. But I’m racking my brain trying to make it work on Interbase 2017. If I remove Case parameters the code works.

SELECT P.ID_PESSOA,
       P.RAZAO_SOCIAL,
       P.NOME_FANTASIA,
       P.ID_CATEGORIA,
       P.TIPO_PESSOA,
       P.NUMERO,
       P.ID_ENDERECO,
       P.BAIRRO,
       E.ENDERECO,
       E.ENDERECO || ', ' || P.NUMERO || ' ' || P.BAIRRO AS ENDERECO_COMPLETO
  FROM PESSOA P
  JOIN ENDERECO E ON (P.ID_ENDERECO = E.ID_ENDERECO)
 WHERE P.ID_CATEGORIA = CASE :ID_CATEGORIA WHEN 0 THEN P.ID_CATEGORIA
                                                  ELSE :ID_CATEGORIA
                                           END

Answer 1

Apparently your idea is to apply the WHERE only when the :ID_CATEGORIA is different from zero. And when the :ID_CATEGORIA for zero, you want all results to be brought.

Try using a OR:

WHERE :ID_CATEGORIA = 0 OR P.ID_CATEGORIA = :ID_CATEGORIA