Ignoring a No_Data_Found Error 3

[lynchg]

I have a table in which I initially use an insert statement to populate all of the rows but only some of the fields in the table. I then want to update the rest of the fields using a separate update.

The way I am doing this is to run a series of Select Into statements to fill variables, then run one Update statement to fill out the rest of the data in each row. This is being done one record at a time while looping through a cursor.

The problem I am having is when there is no data returned by any one of the Select Into statements, I get a NO_DATA_FOUND error which throws me out of my loop and the processing stops.

I am looking for a way to just ignore the error and move on to the next statement. It is perfectly OK for there to be no data for any of the Select Into statements, there isn't always going to be data for every field I am updating.

Thanks in advance for any ideas.

Gerry

 

[Glowworm27]

I have had similar problems, I got around this buy doing a

Select Count(*) into iSomeVariable where field = 'x'

then evaluating the iSomevariable to see if it is greater than 0 (zero) then continue processing.

Its pretty rudimentary but works as planned, and Im sure there is a better way but I don't know it.

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[lynchg]

Thanks, I actually thought about that, but with the No_Data_Found error I was getting on my Select into, I was mistakenly thinking that thia would generate the same error.

What an idiot, the Count() will always return a value, D'oh!

 

[Glowworm27]

no problem Lynchg, I had to go through a similar D'oh! to figure it out as well.

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[cappmgr]

Couldn't you catch the NO_DATA_FOUND exception and handle it accordingly.
Marty

 

[lynchg]

I was looking for a way to try to trap and "bypass" the error, but everything I tried either sent the error to the Exception section of the procedure or I got a syntax error.

Due to the fact that I am working from within a loop I need to handle the whole thing right there and avoid passing the execution off to another spot in the code. I would never get back to the same spot I left off from.

If you have a way to achieve that I would love to see it.

Thanks,

Gerry

 

[lewisp]

How about:

BEGIN
...some code here...
  BEGIN
    SELECT your_stuff
    INTO   something
    WHERE  etc..;
  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
      something := something_else;
  END;
..more code here...
END;

 

[cappmgr]

Well I would make the select statements into functions or procedures and call them inside my loop. Just the way I would do it never said it was better. Can't see using the select count(*) in a loop too much overhead.
Marty

 

[lynchg]

lewisp, I tried what you suggested, I get a syntax error.

cappmgr, I agree with your suggestion, I hate the idea of doubling my Selects within a loop. It would force me to create a bunch of custom functions, but it would definitely be more efficient.

Thanks,

Gerry

 

[cappmgr]

We also do what lewisp does, so it depends, good luck and I am glad you have some alternatives.
marty

 

[lynchg]

My code with the Select Count() statements is running like a dog and is going to be unacceptable.

I tried using lewisp's suggestion, but I get an error when put the Exception segment of code within a loop. How did you get it to work?

 

[lewisp]

Can you give an example of your code?

 

[lynchg]

Here is the relevant code (the p_providerid variable is used for nothing except to perform a meaningless operation in the Exception section):


BEGIN

-- run insert query to load roster data to work table
insert into wrk_comp_diabetes
(measure, memberlastname, memberfirstname,
memberdob, memberid, providerlastname,
providerfirstname, providerid, medgroup)
select qr.rulename, mp.lname, mp.fname,
m.birthdate, qm.memberid, p.lname,
p.fname, qm.providerid, qm.medgroup
from qrulenames@hprdba qr,
memberpersonal@hprdba mp,
member@hprdba m,
provider@hprdba p,
qmemrule@hprdba qm
where qm.ruleid = qr.ruleid (+)
and qm.memberid = mp.memberid
and qm.memberid = m.memberid
and qm.ruleid = -7
and qm.providerid = p.providerid (+)
order by mp.lname, mp.fname;

COMMIT;


-- create a cursor to step thru the records in the work table, then
-- run a series of updates to fill out the data for each record

DECLARE
CURSOR c_comp_diabetes IS
SELECT *
FROM wrk_comp_diabetes;
--FOR UPDATE OF eyeexamdate, eyedoctor, medispecialty,
-- hba1cdate, hba1cresult,
-- ldldate, ldlresult, diabnephrdate,
-- diabnephrcode, textfieldnotes, reviewername;

v_diabetes_data c_comp_diabetes%rowtype;

BEGIN

OPEN c_comp_diabetes;

FETCH c_comp_diabetes into v_diabetes_data;

WHILE c_comp_diabetes%FOUND LOOP

p_memberid := v_diabetes_data.memberid;

SELECT count(hr.svcdate)
INTO p_rec_count
FROM hr_cm_claims@hprdba hr,
provider@hprdba pr
WHERE hr.memberid = p_memberid
AND hr.providerid = pr.providerid
AND hr.subruleid = 358
AND substr(pr.specialty_desc,1,2) = 'Op';

IF p_rec_count <> 0 THEN
SELECT max(hr.svcdate), min(hr.providerid), min(pr.specialty_desc)
INTO v_eyeexamdate, v_eyedoctor, v_medispecialty
FROM hr_cm_claims@hprdba hr,
provider@hprdba pr
WHERE hr.memberid = p_memberid
AND hr.providerid = pr.providerid
AND hr.subruleid = 358
AND substr(pr.specialty_desc,1,2) = 'Op'
AND hr.svcdate = (SELECT max(hr.svcdate)
FROM hr_cm_claims@hprdba hr,
provider@hprdba pr
WHERE hr.memberid = p_memberid
AND hr.providerid = pr.providerid
AND hr.subruleid = 358
AND substr(pr.specialty_desc,1,2) = 'Op')
GROUP BY hr.svcdate;
END IF;

--***

SELECT count(svcdate)
INTO p_rec_count
FROM hr_cm_claims@hprdba
WHERE memberid = v_diabetes_data.memberid
AND subruleid IN (356, 357);

IF p_rec_count <> 0 THEN
SELECT max(svcdate), min(results)
INTO v_hba1cdate, v_hba1cresult
FROM hr_cm_claims@hprdba
WHERE memberid = p_memberid
AND subruleid IN (356, 357)
AND svcdate = (SELECT max(svcdate)
FROM hr_cm_claims@hprdba
WHERE memberid = p_memberid
AND subruleid IN (356, 357))
GROUP BY svcdate;
END IF;

--***
SELECT count(svcdate)
INTO p_rec_count
FROM hr_cm_claims@hprdba
WHERE memberid = v_diabetes_data.memberid
AND subruleid IN (360, 361)
AND results = (SELECT min(results)
FROM hr_cm_claims@hprdba
WHERE memberid = v_diabetes_data.memberid
AND subruleid IN (360, 361));

IF p_rec_count <> 0 THEN
SELECT max(svcdate), min(results)
INTO v_ldldate, v_ldlresult
FROM hr_cm_claims@hprdba
WHERE memberid = v_diabetes_data.memberid
AND subruleid IN (360, 361)
AND results = (SELECT min(results)
FROM hr_cm_claims@hprdba
WHERE memberid = v_diabetes_data.memberid
AND subruleid IN (360, 361))
GROUP BY results;
END IF;

UPDATE wrk_comp_diabetes
SET eyeexamdate = v_eyeexamdate, eyedoctor = v_eyedoctor,
medispecialty = v_medispecialty,
hba1cdate = v_hba1cdate, hba1cresult = v_hba1cresult,
ldldate = v_ldldate, ldlresult = v_ldlresult
WHERE memberid = v_diabetes_data.memberid
AND providerid = v_diabetes_data.providerid;

FETCH c_comp_diabetes into v_diabetes_data;

COMMIT;

END LOOP;

CLOSE c_comp_diabetes;

COMMIT;


END;

EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
p_providerid := 1;
END;

 

[SantaMufasa]

Lynch,

The strategy that Lewis suggests works fine. I use it very frequently, and you can dispense with the "SELECT COUNT(*)..." test, as well. The key is turning the "SELECT" statement into a mini-block of PL/SQL code, complete with its own "BEGIN"..."EXCEPTION"..."END". (You can certainly embed this techique successfully within a LOOP, as well.) :

DECLARE
    (your variable definitions here);
BEGIN
    (a bunch of code);
    for (some kind of loop driver) LOOP
        [b]BEGIN
            SELECT <expressions> INTO <variables>
                FROM <table(s)>
                WHERE <conditional expresssions>;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                NULL; -- this means don't do anything special
        END;[/b]
    end loop;
    (more main-line code);
EXCEPTION
    WHEN OTHERS THEN
        (do something cool to handle generic errors.)
END;

Let us know if this works to your advantage.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 20:54 (18Feb05) UTC (aka "GMT" and "Zulu"),
@ 13:54 (18Feb05) Mountain Time

Do you use Oracle and live or work in Utah, USA? Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[lewisp]

You can perform a null operation with the command [tt]NULL;[/tt]

If for example you want to trap the NO_DATA_FOUND from your SELECT then do it this way:

    BEGIN
        SELECT max(svcdate), min(results)
        INTO   v_hba1cdate, v_hba1cresult
        FROM   hr_cm_claims@hprdba
        WHERE  memberid = p_memberid
        AND    subruleid IN (356, 357)
        AND    svcdate = (SELECT max(svcdate)
                          FROM   hr_cm_claims@hprdba
                          WHERE  memberid = p_memberid
                          AND    subruleid IN (356, 357))
        GROUP BY svcdate;
    EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
        p_rec_count := 0;
    END;

...or alternatively....

    BEGIN
      SELECT max(svcdate), min(results)
        INTO   v_hba1cdate, v_hba1cresult
        FROM   hr_cm_claims@hprdba
        WHERE  memberid = p_memberid
        AND    subruleid IN (356, 357)
        AND    svcdate = (SELECT max(svcdate)
                          FROM   hr_cm_claims@hprdba
                          WHERE  memberid = p_memberid
                          AND    subruleid IN (356, 357))
        GROUP BY svcdate;
    EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
        NULL;
    END;

 

[lewisp]

Blast - I obviously type too slowly! ;-)

...and ironically too fast at the same time. The EXCEPTION in my first example should be

    EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
        v_hba1cdate := NULL;
        v_hba1cresult := NULL;
      END;

 

[lynchg]

Thanks to all of you people, I am running the proc right now, I'll see how it goes.

lewisp, your last little snippet of code helped me remember to empty out those variables so I wouldn't unintentionally enter data from the previous loop.

Gerry