CODEWITHSUNDEEP
sqloracle-databasedecode
homer
homer

Reputation: 433

Use decode in sql query

Experts...

I'm trying to fix below simple query to include null condition.

I can use "IS NOT NULL" in the where clause and ignore the record but again that won't be the best way. Want to fix this in right way.

Below current decode process list all processes and replace "1234@xzy", "1234@abc" with JDBC. This decode doesn't work properly in script when there is null in the list.

Question: 1. Is it possible to modify current decode to replace null with NA?

Query in oracle:

SELECT osuser, machine,
       DECODE (process, '1234', 'JDBC', substr(process, 1, instr(process || '@','@')-1)) "PID"
FROM v$session
WHERE username IS NOT NULL;

Expected Result:
=============== 
4567    
78960    
4575    
JDBC    
JDBC    
NA    
9658

Process list:
=============
4567    
78960    
4575    
1234@abc    
1234@xyz    
   (null)
9658

Upvotes: 1

Views: 897

Answers (1)

Mureinik
Mureinik

Reputation: 312404

You could wrap the decode expression with an nvl expression:

SELECT osuser, machine,
       NVL(DECODE (process, 
                   '1234', 'JDBC', 
                    substr(process, 1, instr(process || '@','@')-1)), 
           'NA') "PID"
FROM   v$session
WHERE  username IS NOT NULL;

Upvotes: 1

Related Questions