Here is the procedure with the problem:
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 condition where the procedure checks if the input parameters IN_CARTAO and IN_CPF are filled, if they include the condition, if it is not fulfilled the idea was to ignore the parameters.
I tried to implement the code below after the last AND condition inside the where, however it does not accept IF directly inside the 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 is 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)