Hi experts,
Could you please help in the following scenario :
i_variable is a NUMBER;
Table t1 and t2
c1 NOT NULL NUMBER(8)
c2 NOT NULL DATE
c3 NOT NULL DATE
I have the following code as part of a function (which returns boolean):
l_date date := sysdate + 23
l_date_1 date := sysdate + 30
l_flag char(1);
cursor c1 is select 'X' from t1 where c1 = i_variable;
/* Existing code */
Open Cursor c1
fetch c1 into v1
if c1%NOTFOUND
THEN
insert into t1 (c1,c2,c3)
select c1,MAX(c2),
case when min(c3) < l_date
then l_date
else min(c3)
end,
from t2 where c1 = i_variable
else
UPDATE t1
set (c1,c2,c3) = select c1,MAX(c2),
case when min(c3) < l_date
then l_date
else min(c3)
end,
from t2 a,t3 b
where <.... some join logic....>
end if;
close c1;
Now I have a flag (l_flag) based on which I must choose either l_date or l_date_1 variables for date comparison.
My new code is :
Open Cursor c1
fetch c1 into v1
if c1%NOTFOUND
THEN
insert into t1 (c1,c2,c3)
select c1,MAX(c2),
case when min(c3) < DECODE(l_flag,'X',l_date_1,l_date)
then DECODE(l_flag,'X',l_date_1,l_date)
else min(c3)
end,
from t2 where c1 = i_variable
else
UPDATE t1
set (c1,c2,c3) = select c1,MAX(c2),
case when min(c3) < DECODE(l_flag,'X',l_date_1,l_date)
then DECODE(l_flag,'X',l_date_1,l_date)
else min(c3)
end,
from t2 a,t3 b
where <.... some join logic....>
end if;
close c1;
However, I am observing that the result is incorrect with the new code.
Could you please suggest changes so that I can get the correct values as part of the INSERT / UPDATE.
A quick help would be appreciated.
Regards,
Haider