CODEWITHSUNDEEP
sqloracle10g
Developer GG
Developer GG

Reputation: 11

NVL in Where clause doesn't return records which has null values

I used this SELECT statement:

SELECT 
 ID 
 ,SUB_TYPE    
 ,SERVICE_ID  
 ,MENU_TYPE   
 ,MENU_DESCRIPTION  
    FROM MY_TABLE 
    WHERE   SUB_TYPE     = NVL('' , SUB_TYPE)     
        AND SERVICE_ID   = NVL('' , SERVICE_ID)   
        AND MENU_TYPE    = NVL('' , MENU_TYPE)

The rows that have null MENU_TYPE don't return , but I want these records to return

Thanks in advance

Upvotes: 1

Views: 4643

Answers (2)

redcayuga
redcayuga

Reputation: 1251

Are you looking for rows in which all three columns are null? If so, you want:

WHERE SUB_TYPE    is null
 AND  SERVICE_ID  is null
 AND  MENU_TYPE   in null

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132630

NULL is never equal to NULL. And in Oracle, '' is NULL.

Try this:

((:P_MENU_TYPE IS NULL AND MENU_TYPE IS NULL) OR MENU_TYPE = :P_MENU_TYPE)

(I put a bind variable :P_MENU_TYPE where you had '' because the SQL wouldn't make sense with ''!)

If what you really mean is, MENU_TYPE must match the parameter if the parameter is not null, and may be anything including null when the parameter is null then simply:

(:P_MENU_TYPE IS NULL OR MENU_TYPE = :P_MENU_TYPE)

Upvotes: 1

Related Questions