Oracle - IF Statement into WHERE Clause

Question

I should translate the C# code into a stored procedure in Oracle.

I wrote a stored procedure with case when in the where but the right data is not returned. Where am I wrong?

The problem is to manage the IFs of the input parameters.

Oracle:

CREATE OR REPLACE PROCEDURE SP_S_RICCFEMESSE 
(
    NUMEROCV        IN VARCHAR2,
    PROFILO         IN NUMBER,
    SALAES          IN NUMBER,
    DATAINIZIO      IN VARCHAR2,
    DATAFINE        IN VARCHAR2,
    ric_carteemesse OUT SYS_REFCURSOR
)
AS 
BEGIN
    OPEN ric_carteemesse FOR
       SELECT DISTINCT A.*
                ,DESCARDPRFCFR AS TIPO_CARTA
                ,TO_CHAR(DATNAS,'DD/MM/YYYY') AS DATA_DI_NASCITA 
                ,TO_CHAR(A.DATIVOPSA,'DD/MM/YYYY HH24:MI:SS') AS DATA_EMISSIONE 
                ,TO_CHAR(DATINIVALRTA,'DD/MM/YYYY') AS INIZIO_VALIDITA_CARTA 
                ,TO_CHAR(DATFINIVALRTA,'DD/MM/YYYY') AS FINE_VALIDITA_CARTA 
                ,C.S_DENOMINAZIONE AS SALA_ES 
                ,CASE 
                    WHEN NVL(SCDUP,0) = '0' 
                    THEN 'NO' 
                    WHEN SCDUP > 0 
                    THEN 'SI' 
                END AS DUPLICATO 
FROM 
DEMIRTAFRC A 
INNER JOIN SMART_CARD B 
    ON A.CODSRE = B.COD_SM 
LEFT JOIN RIVENDITA C 
    ON B.S_POSTAZIONE_UM = TO_CHAR(C.COD_RIVENDITA) 
INNER JOIN DANACARDPRFCFR D ON A.CODPRF= D.CARDPRFCFR 
WHERE 
    (B.D_ELETTRIFICAZIONE BETWEEN TO_DATE(DATAINIZIO,'DD/MM/YYYY') AND TO_DATE(DATAFINE,'DD/MM/YYYY') 
    AND A.MDLCSG = 'CBM' AND CODSRE IS NOT NULL)
    AND NUMEROCV = (CASE WHEN NUMEROCV <> '' 
                         THEN A.CODRTA
                         ELSE null 
                    END)
    AND PROFILO = (CASE WHEN PROFILO <> 0 
                        THEN B.COD_REQUISITI
                        ELSE null 
                   END)
    AND SALAES = (CASE WHEN SALAES <> 999
                       THEN B.S_POSTAZIONE_UM
                       ELSE null 
                  END)
ORDER BY A.DATETR DESC;

END SP_S_RICCFEMESSE;

This is code C# to replace:

if (_NumCV.ToString().Trim() != "")
                _cmd.CommandText += " AND A.CODRTA = '" + _NumCV + "' ";

            if (_Profilo != 0)
                _cmd.CommandText += " AND B.COD_REQUISITI = " + _Profilo;

            if (_SalaES != 999)
                _cmd.CommandText += " AND B.S_POSTAZIONE_UM = '" + _SalaES + "' ";

Answer 1

NULL never equals NULL. So if any of the CASE statements go down the ELSE branch the statement will evaluate to FALSE. Also, in Oracle empty string is the same as NULL (yes, it's controversial but Oracle's been that way for almost forty years, it's just the way it is) which means this is always going to be FALSE: NUMEROCV <> ''.

Simple solution is, don't use CASE in the WHERE clause. This is much clearer:

AND (NUMEROCV is null or NUMEROCV = A.CODRTA)
AND (PROFILO = 0 or PROFILO = B.COD_REQUISITI)
AND (SALAES = 999 or SALAES = B.S_POSTAZIONE_UM))

Answer 2

Do you my have null value in columns PROFILO, SALAES, NUMEROCV. If yes then you must set nvl on this columns in where part.

Because you c# say that you may have null try this:

 CREATE OR REPLACE PROCEDURE SP_S_RICCFEMESSE 
(
    NUMEROCV        IN VARCHAR2,
    PROFILO         IN NUMBER,
    SALAES          IN NUMBER,
    DATAINIZIO      IN VARCHAR2,
    DATAFINE        IN VARCHAR2,
    ric_carteemesse OUT SYS_REFCURSOR
)


AS 
BEGIN

OPEN ric_carteemesse FOR
SELECT DISTINCT A.*
                ,DESCARDPRFCFR AS TIPO_CARTA
                ,TO_CHAR(DATNAS,'DD/MM/YYYY') AS DATA_DI_NASCITA 
                ,TO_CHAR(A.DATIVOPSA,'DD/MM/YYYY HH24:MI:SS') AS DATA_EMISSIONE 
                ,TO_CHAR(DATINIVALRTA,'DD/MM/YYYY') AS INIZIO_VALIDITA_CARTA 
                ,TO_CHAR(DATFINIVALRTA,'DD/MM/YYYY') AS FINE_VALIDITA_CARTA 
                ,C.S_DENOMINAZIONE AS SALA_ES 
                ,CASE 
                    WHEN NVL(SCDUP,0) = '0' 
                    THEN 'NO' 
                    WHEN SCDUP > 0 
                    THEN 'SI' 
                END AS DUPLICATO 
FROM 
DEMIRTAFRC A 
INNER JOIN SMART_CARD B 
    ON A.CODSRE = B.COD_SM 
LEFT JOIN RIVENDITA C 
    ON B.S_POSTAZIONE_UM = TO_CHAR(C.COD_RIVENDITA) 
INNER JOIN DANACARDPRFCFR D ON A.CODPRF= D.CARDPRFCFR 
WHERE 
    (B.D_ELETTRIFICAZIONE BETWEEN TO_DATE(DATAINIZIO,'DD/MM/YYYY') AND TO_DATE(DATAFINE,'DD/MM/YYYY') 
    AND A.MDLCSG = 'CBM' AND CODSRE IS NOT NULL)
    AND nvl(NUMEROCV, ' ') = (CASE WHEN nvl(NUMEROCV, ' ') <> ' ' 
                         THEN A.CODRTA
                         ELSE null 
                    END)
    AND nvl(PROFILO, 0) = (CASE WHEN nvl(PROFILO, 0) <> 0 
                        THEN B.COD_REQUISITI
                        ELSE null 
                   END)
    AND NVL(SALAES, 0) = (CASE WHEN NVL(SALAES, 0) <> 999
                       THEN B.S_POSTAZIONE_UM
                       ELSE null 
                  END)
ORDER BY A.DATETR DESC;

END SP_S_RICCFEMESSE;

Answer 3

If you have a lot of criterias you can build sql dynamically

Declare sql_statement

sql_statement VARCHAR2(...);

sql_statement := 'SELECT .... WHERE ' // your select

IF PROFILO <> 0 THEN // your criteria here
    sql_statement := sql_statement || ' AND B.COD_REQUISITI = ' || PROFILO // your criteria here;

EXECUTE IMMEDIATE(sql_statement);