Selecting, then testing for existance

bnordgren commented 8 years ago

in the viirs_threshold_2_fireevents method (and perhaps elsewhere) I see :

SELECT * from ... LIMIT 1 into dumrec ; 
IF EXISTS (SELECT * from ... LIMIT 1) THEN
    blah
END

We may be able to cut the number of select statements in half if we take the approach here: http://stackoverflow.com/questions/7163996/how-to-select-into-a-variable-in-pl-sql-when-the-result-might-be-null

BEGIN
    SELECT * from ... LIMIT 1 into dumrec ; 
EXCEPTION
     WHEN NO_DATA_FOUND THEN 
        NULL ; 
 END;

calbury commented 8 years ago

I will look into this. I’ve never been satisfied with how complicated these queries are.

[Forest Service Shield]

Carl Albury Remote Sensing Specialist

Forest Service Contractor Remote Sensing Applications Center

p: 801-975-3351 calbury@fs.fed.usmailto:calbury@fs.fed.us

2222 West 2300 South Salt Lake City, UT 84119 www.fs.fed.ushttp://www.fs.fed.us/ [USDA Logo]http://usda.gov/ [Forest Service Twitter] https://twitter.com/forestservice [USDA Facebook] https://www.facebook.com/pages/US-Forest-Service/1431984283714112

Caring for the land and serving people

From: bnordgren [mailto:notifications@github.com] Sent: Tuesday, February 16, 2016 10:49 PM To: firelab/viirs_ba viirs_ba@noreply.github.com Cc: Albury, Carl -FS calbury@fs.fed.us Subject: [viirs_ba] Selecting, then testing for existance (#7)

in the viirs_threshold_2_fireevents method (and perhaps elsewhere) I see :

SELECT * from ... LIMIT 1 into dumrec ;

IF EXISTS (SELECT * from ... LIMIT 1) THEN

blah

END

We may be able to cut the number of select statements in half if we take the approach here: http://stackoverflow.com/questions/7163996/how-to-select-into-a-variable-in-pl-sql-when-the-result-might-be-null

BEGIN

SELECT * from ... LIMIT 1 into dumrec ;

EXCEPTION

 WHEN NO_DATA_FOUND THEN

    NULL ;

END;

— Reply to this email directly or view it on GitHubhttps://github.com/firelab/viirs_ba/issues/7.

This electronic message contains information generated by the USDA solely for the intended recipients. Any unauthorized interception of this message or the use or disclosure of the information it contains may violate the law and subject the violator to civil or criminal penalties. If you believe you have received this message in error, please notify the sender and delete the email immediately.

bnordgren commented 8 years ago

I restructured the threshold_burned part. It replaces the loop with a temporary table and single insert/update statements. Further pursuit of this topic should go on #16.

firelab / viirs_ba

Selecting, then testing for existance #7