how to set a null value to default in oracle sql

Question

How do I set an empty set or null value to a default value like 1?

So far, I have this statement, but in case I get null values i want to handle that:

select case when count(*)=0 
                   then 0 
                 else 1 
                   end OUTPUT 
from TESTTBL 
where timestamp = to_char(sysdate-1, 'yyyymmdd')||'0000';

well what if i have no values for my timestamp criteria? then count would be always 0 right? i'm just thinking in the case if i have no records. — jdamae
– jdamae, Commented Jul 20, 2011 at 22:11
if there are no records that fit that condition - you'll get empty recordset, not null — zerkms
– zerkms, Commented Jul 20, 2011 at 22:15
ok, then for empty set. How can I set an empty set to a value of 1? — jdamae
– jdamae, Commented Jul 20, 2011 at 22:17

user747858 · Accepted Answer · 2011-07-20 22:06:33Z

6

Do you mean to check for Null value and set as some default, if so

select nvl(column_name,'DEFAULT') from TESTBL where timestamp = to_char(sysdate-1,   'yyyymmdd')||'0000';

answered Jul 20, 2011 at 22:06

user747858

Sign up to request clarification or add additional context in comments.

Comments

ypercubeᵀᴹ · Accepted Answer · 2011-07-20 23:00:19Z

5

SELECT CASE WHEN EXISTS
                   ( SELECT * 
                     FROM TESTTBL 
                     WHERE timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000'
                   )
            THEN 1
            ELSE 0
       END AS OUTPUT
FROM dual

EDIT

Added FROM dual as Oracle does not allow SELECT without FROM clause.

edited Jul 20, 2011 at 23:00

answered Jul 20, 2011 at 22:35

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

2 Comments

zerkms Over a year ago

@ypercube: +1'd, but I vote for changing from SELECT * to SELECT null and for adding FROM dual in the end

ypercubeᵀᴹ Over a year ago

I doubt there is any difference in performance. We could well use SELECT 1/0 : stackoverflow.com/questions/1597442/…

Mangesh · Accepted Answer · 2011-07-20 22:20:54Z

Here you go

SELECT DECODE(count(*),0,0,
                      1) OUTPUT
   FROM TESTTBL 
   WHERE TIMESTAMP = TO_CHAR(SYSDATE-1, 'yyyymmdd')||'0000';

Use Decode like

SELECT supplier_name,
        decode(supplier_id, 10000,  'Google',
                            10001,  'Microsoft'                                
                           'Sony') result
 FROM suppliers;

equivalent to

IF supplier_id = 10000 THEN
     result := 'Google';

ELSIF supplier_id = 10001 THEN
    result := 'Microsoft';

ELSE
    result := 'Sony';    
END IF;

Or Use coalesce

SELECT coalesce( address1, address2) result
FROM suppliers;

which is equivalent to

IF address1 is not null THEN
     result := address1;

ELSIF address2 is not null THEN
    result := address2;

ELSE
    result := null;

END IF;

Collectives™ on Stack Overflow

how to set a null value to default in oracle sql

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related