Case with parameter in Interbase 2017

1

Good night everyone, I've always used Firebird, but I decided to try the interbase 2017 for mobile applications. The following SQL works normally in Firebird and SQLite. But I'm breaking my head to try to make it work in Interbase 2017. If I remove the parameters from the Case 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
    
asked by anonymous 28.07.2018 / 03:11

1 answer

0

It seems that your idea is to apply WHERE only when :ID_CATEGORIA is non-zero. And when :ID_CATEGORIA is zero, you want all results to be brought in.

Try using a OR :

WHERE :ID_CATEGORIA = 0 OR P.ID_CATEGORIA = :ID_CATEGORIA
    
28.07.2018 / 07:36