I have the stored procedure below hat is working fine. I'd like, whoever, to be able to do a check to see if the dataset with YesterdayDate exists and, if not, replace YesterdayDate with TodayDate.
I was trying something like cheking if this count select count(1) from TableName where DATASET_DATE = YesterdayDate
is 0, but I cannot find a way, assuming there is any, to add this check to the code without having to revamp or break everything else.
Stored Procedure:
CREATE OR REPLACE function DO_STUFF
(TodayDate date, YesterdayDate date)
return integer is status integer;
BEGIN
DECLARE
CURSOR c_stuff
IS
SELECT
<several_fields>
FROM
TableName TODAY
LEFT JOIN TableName YESTERDAY
ON TODAY.KEY1 = YESTERDAY.KEY1
WHERE TODAY.DATASET_DATE = TodayDate
AND YESTERDAY.DATASET_DATE = YesterdayDate
ORDER BY
TODAY.KEY1 ASC;
BEGIN
FOR r_lines IN c_stuff
LOOP
<do_some_stuff>
END LOOP;
END;
COMMIT;
STATUS := 0;
return status;
END
/
Does anyone have an idea on how this can be accomplished?
-- Edited to clarify: I do need to use both predicates. In the <do_some_stuff> section I'm mostly doing calculations and comparing the two datasets (Today versus yesterday) and updating some fields with the results. What I want to accomplish here is to deal with the situation where the Yesterday process was not ran and there is no yesterday dataset, by comparing today against today and avoiding incorrect data on the results.
I am not sure what you are trying to do with the function. Whatever I understood from your first line of question, You can try the below code -
CREATE OR REPLACE function DO_STUFF(TodayDate date,
YesterdayDate date)
RETURN INTEGER
IS
new_date DATE;
CNT NUMBER;
BEGIN
SELECT COUNT(*)
INTO CNT
FROM TableName TODAY
LEFT JOIN TableName YESTERDAY ON TODAY.KEY1 = YESTERDAY.KEY1
WHERE TODAY.DATASET_DATE = TodayDate
AND YESTERDAY.DATASET_DATE = YesterdayDate
ORDER BY TODAY.KEY1 ASC;
IF CNT = 0 THEN
RETURN TodayDate;
ELSE
RETURN YesterdayDate;
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN '1900-01-01';
END;