CODEWITHSUNDEEP
sqloracle-databaseexceptionoracle10g
daria
daria

Reputation: 141

exception no-data-found with select 1 from dual

i'm trying to update a table when l_num equals 2 and a certain condition is met. the condition is actually not having a single record for an defined id.

for that i used the select 1 from dual. the problem is that the l-test is all the time returning 0 (meaning that there's no data found).

any help would be welcome.

here's the code:

CASE l_num
    WHEN 2
    THEN
       BEGIN
          SELECT 1
            INTO l_test
            FROM DUAL
           WHERE NOT EXISTS (
                    SELECT 1
                      FROM table t1, table t2
                     WHERE t1.id = l_id
                       AND t1.status = 'D'
                       AND t1.code = t2.code);
       EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
             l_test := 0;
       END;
                                                                                           DBMS_OUTPUT.put_line ('  l_test when pr l_num =2 :' || l_test);
       IF l_test = 1
       THEN
          UPDATE ....

thanx

Upvotes: 1

Views: 1229

Answers (1)

Ollie
Ollie

Reputation: 17538

Couldn't you just count the number of matches in your tables?

(Also, you should really start using ANSI SQL syntax not the old SQL-86)

      SELECT count(1)
        INTO l_test
        FROM table t1
       INNER JOIN table t2 USING (code)
       WHERE t1.id = l_id
         AND t1.status = 'D';

If the count is zero then there were no matching ID's and if it's greater than zero then matches are present.

This also simplifies your code as you don't have to check for the NO_DATA_FOUND as the count will always return a value (zero or more).

Hope it helps...

Upvotes: 2

Related Questions