IF condition inside a WHERE - ORACLE PL/SQL

Question

See below for details of the problem in question:

CREATE OR REPLACE PROCEDURE NOVODIA.PRC_PACIENTES_INATIVOS_95
(
  --PARAMETROS
  IN_CPF      IN   VARCHAR2,
  IN_CARTAO   IN   VARCHAR2,
  OUT_CURSOR  OUT  SYS_REFCURSOR
)

IS
  --VARIAVEIS
  V_COD_CLIENTE VARCHAR2(10) := 'CT000326';      -- CUSTOMER_ID DO CLIENTE
  V_EAN_VICTOZA VARCHAR2(14) := '7897705201770'; -- CODIGO EAN DO MEDICAMENTO VICTOZA
  V_QTD_COMPRA  NUMBER       := 3;               -- QUANTIDADE DE MEDICAMENTOS COMPRADOS

BEGIN

  OPEN OUT_CURSOR FOR

    SELECT ANO_MES,
           DATA,
           COD_CLIENTE,
           CLIENTE,
           M.MEMBER_FIRST_NAME || M.MEMBER_LAST_NAME NOME_CLIENTE,
           M.ALT_MEMBER_ID CPF, --CPF
           G.SEXO_PACIENTE SEXO,
           G.E_MAIL EMAIL,
           G.TELEFONE_1 TELEFONE1,
           G.TELEFONE_2 TELEFONE2,
           M.ADDRESS_1 ENDERECO1,
           M.ADDRESS_2 ENDERECO2,
           G.END_NUMERO NUMERO,
           G.END_COMPLEMENTO COMPLEMENTO,
           M.CITY CIDADE,
           M.STATE ESTADO,
           M.ZIP_CODE CEP,
           A.MEDICO,
           A.MEDICO_LINHA,
           A.CRM,
           A.UF_CRM,
           A.CNPJ,
           A.NOME_FANTASIA_LOJA,
           A.RAZAO_SOCIAL_LOJA,
           A.CIDADE,
           A.UF,
           A.CARTAO,
           A.AUTORIZACAO,
           A.CUPOM_FISCAL,
           A.FAMILIA,
           A.CLASSE_TERAPEUTICA,
           A.APRESENTACAO,
           A.GENERICO,
           A.EAN,
           A.QTDE,
           A.PS,
           A.PF,
           A.PMC,
           A.DESCONTO,
           A.PV,
           A.PMC_TOTAL,
           A.PV_TOTAL
      FROM GWPROD.DW_PRG_ANALITICO_4 A
     inner join IIS.MEMBER_GENERICO G
        on (g.customer_id = a.cod_cliente and g.member_id = a.cartao)
     inner join MEMBER M
        on (m.CUSTOMER_ID = G.CUSTOMER_ID AND M.MEMBER_ID = G.MEMBER_ID AND
           M.CLIENT_ID = G.CLIENT_ID AND M.CLIENT_GROUP_ID = G.CLIENT_GROUP_ID AND
           M.Relationship = G.RELATIONSHIP)
     WHERE A.COD_CLIENTE = V_COD_CLIENTE
          AND NOT EXISTS( SELECT NULL FROM STATUS_CANCELAMENTO_RECEITAS S WHERE S.RECCOD = A.AUTORIZACAO )
       AND A.EAN = V_EAN_VICTOZA -- COD_EAN_VICTOZA
       AND A.QTDE = V_QTD_COMPRA
       AND DATA <= TRUNC(SYSDATE - 95)

    AND EXISTS( SELECT NULL FROM GWPROD.PRIMEIRA_COMPRA_VIDA P  -- VALIDA SE É A PRIMEIRA COMPRA DO CLIENTE
                WHERE P.CUSTOMER_ID = m.customer_id
                  AND P.MEMBER_ID = m.member_id
                  and p.client_id = m.client_id
                  and p.client_group_id = m.client_group_id
                  and p.person_code = m.person_code
                  AND P.RECCOD = A.AUTORIZACAO )

    AND NOT EXISTS( SELECT NULL FROM GWPROD.DW_PRG_ANALITICO_4 B-- VERIFICA SE CLIENTE NÃO COMPROU NENHUM VICTOZA NOS ÚLTIMOS 95 DIAS
                    WHERE B.COD_CLIENTE = A.COD_CLIENTE
                      AND B.CARTAO = A.CARTAO
                      AND B.EAN = A.EAN
                      AND DATA > TRUNC( SYSDATE-95 ) )



    ORDER BY A.CARTAO, A.DATA;

END;

PROBLEM IN QUESTION: I need that in the Where condition the Procedure check that the input parameters IN_CARTAO and IN_CPF are fulfilled, if they are including the condition, if not fulfilled the idea was to ignore the parameters.

I tried to implement the code below after the last AND condition inside Where but it does not accept IF directly inside Where:

IF IN_CARTAO <> null AND IN_CARTAO <> '' AND IN_CPF <> null AND IN_CPF <> '' THEN
  AND A.CARTAO = IN_CARTAO
  AND M.ALT_MEMBER_ID = IN_CPF
ELSIF IN_CARTAO <> null AND IN_CARTAO <> '' THEN
  AND A.CARTAO = IN_CARTAO
ELSIF IN_CPF <> null AND IN_CPF <> '' THEN
  AND M.ALT_MEMBER_ID = IN_CPF
END IF;

Below another unsuccessful attempt, this time using CASE WHEN:

AND A.CARTAO = (CASE WHEN IN_CARTAO <> NULL AND IN_CARTAO <> '' THEN IN_CARTAO                        
ELSE 'NOT NULL' END)

AND M.ALT_MEMBER_ID = (CASE WHEN IN_CPF <> NULL AND IN_CPF <> '' THEN IN_CPF                        
ELSE 'NOT NULL' END)

Answer 1

Assemble a group of Ands the Ors

(
(IN_CARTAO <> null AND IN_CARTAO <> '' AND IN_CPF <> null AND IN_CPF <> '' 
  AND A.CARTAO = IN_CARTAO
  AND M.ALT_MEMBER_ID = IN_CPF)
  OR
(IN_CARTAO <> NULL AND IN_CARTAO <> '' 
  AND A.CARTAO = IN_CARTAO)
 OR
(IN_CPF <> null AND IN_CPF <> '' 
  AND M.ALT_MEMBER_ID = IN_CPF)
)  

Answer 2

If to have the condition the two parameters should be informed, I think it solves:

AND (
     (in_cartao IS NOT NULL AND in_cpf IS NOT NULL 
       AND a.cartao = in_cartao AND m.alt_member_id = in_cpf) 
       OR (in_cartao IS NULL OR in_cpf IS NULL)
    )

Now, if you are going to treat the parameters individually, for example, if I inform Cpf, add the condition to Cpf, and if the card is informed add the condition to the card, then it should be like this:

AND (in_cartao IS NULL OR a.cartao = in_cartao) 
AND (in_cpf IS NULL OR m.alt_member_id = IN_cpf)

Answer 3

If the idea is to use the parameters only when they are filled in, then you can do it as follows:

...
WHERE A.CARTAO = NVL(IN_CARTAO, A.CARTAO)
AND M.ALT_MEMBER_ID = NVL(IN_CPF, M.ALT_MEMBER_ID)

See more about the NVL function