Oracle nvl not able to handle empty string even after using is not null

Question

I am trying to return empty or dummy string if select query returns null.I tried using dummy value in NVL(colum,'dummy'), but I am still getting no data found error. Below is what I tried:

1.

  SELECT de.destination INTO l_sub_ent

  FROM dest de

  where de.destination='somevalue' AND de.destination IS NOT NULL;

2.

  SELECT COALSECE(de.destination, 'dummy') INTO l_sub_ent

  FROM dest de

  where de.destination ='some value';

3.

  SELECT NVL(de.desc_en, 'dummy') INTO l_sub_ent

  FROM dest de

  where de.destination ='some value';

Kaushik Nayak · Accepted Answer · 2017-10-06 05:12:58Z

2

Problem is not that your de.destination is NULL. There are no records for the condition de.destination='somevalue'. Handle it something like this.

SELECT Count(1)
INTO   v_count
FROM   dest de
WHERE  de.destination='somevalue';

IF v_count > 0 THEN
  SELECT NVL(de.destination,'dummy')
  INTO   l_sub_ent
  FROM   dest de
  WHERE  de.destination='somevalue';
END IF;

edited Oct 6, 2017 at 5:12

answered Oct 6, 2017 at 5:07

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

Comments

Ubaid Ashraf · Accepted Answer · 2017-10-06 05:19:10Z

0

I was able to solve using below :

SELECT NVL(MIN(val), 'dummy') INTO l_sub_ent

  FROM dest de

  where de.destination ='some value';

answered Oct 6, 2017 at 5:19

Ubaid Ashraf

8855 gold badges16 silver badges45 bronze badges

Collectives™ on Stack Overflow

Oracle nvl not able to handle empty string even after using is not null

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related