On Oct 27, 3:03 pm, Gayathri <[email protected]> wrote:
> Thanks Mike,
>
> But these are part of Update statement.
>
> something like this:
>
> Update mbr_id = (Query 1)
> where exists (Query 1)
>
> which sometime would return with no updates
>
> Then I do:
>
> Update mbr_id = (Query 2)
> where exists (Query 2)
>
This might work for you:
declare
v_start_dt date := to_date('12/01/2009', 'mm/dd/yyyy');
v_end_dt date := to_date(NULL, 'mm/dd/yyyy');
begin
update mbr
set mbr_id=(SELECT MIN(b.mbr_id) KEEP(DENSE_RANK FIRST ORDER BY
b.mbr_dt_start)
FROM mbr b
WHERE b.status_id IN (13, 14)
AND b.mbr_b_id = 47115
AND (b.mbr_dt_start != v_start_dt and NVL(b.mbr_dt_end,
TO_DATE('12/31/9999', 'mm/dd/yyyy')) > v_start_dt and
NVL(b.mbr_dt_end, TO_DATE('12/31/9999', 'mm/dd/yyyy')) <
NVL(v_end_dt, TO_DATE('12/31/9999', 'mm/dd/yyyy')))
union
SELECT MAX(b.mbr_id) KEEP(DENSE_RANK FIRST ORDER BY b.mbr_dt_start
desc)
FROM mbr b
WHERE b.status_id IN (13, 14)
AND b.mbr_b_id = 47115
AND v_end_dt is not null
and v_end_dt > b.mbr_dt_start)
where exists
(SELECT MIN(b.mbr_id) KEEP(DENSE_RANK FIRST ORDER BY
b.mbr_dt_start)
FROM mbr b
WHERE b.status_id IN (13, 14)
AND b.mbr_b_id = 47115
AND (b.mbr_dt_start != v_start_dt and NVL(b.mbr_dt_end,
TO_DATE('12/31/9999', 'mm/dd/yyyy')) > v_start_dt and
NVL(b.mbr_dt_end, TO_DATE('12/31/9999', 'mm/dd/yyyy')) <
NVL(v_end_dt, TO_DATE('12/31/9999', 'mm/dd/yyyy')))
union all
SELECT MAX(b.mbr_id) KEEP(DENSE_RANK FIRST ORDER BY b.mbr_dt_start
desc)
FROM mbr b
WHERE b.status_id IN (13, 14)
AND b.mbr_b_id = 47115
AND v_end_dt is not null
and v_end_dt > b.mbr_dt_start);
exception
when others then
dbms_output.put_line('Error: ' || SQLERRM);
end;
/
David Fitzjarrell
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
