PL/SQL Help

[kokiri]

Need help on my script. I'm trying to capture all data but doc_table key column(stored date) is messed up and I need to somehow to capture the data that does not meet the critieria. For example, if stored date (key) is documented before entertime than I want key field to be entertime.

Thanks for your help.
------------------------------------------------
declare
cursor c_unit is
select distinct r.patid, r.minorit, r.key, r.name, u.unit, u.entertime , u.exittime
from (select distinct a.patid, a.minorit, a.key, p.name
from doc_table a,
name_table p
where a.patid=p.patid ) r ,
unit_table u
where r.patid=u.patid
and r.key between u.entertime and nvl(u.exittime, sysdate) ;

begin
For v_unit In c_unit loop
if v_unit.key < v_unit.entertime then
V_unit.key := v_unit.entertime ;
dbms_output.put_line( v_unit.ptname ||', '||
v_unit.unit ||', '||
v_unit.patid ||', '||
v_unit.key ||', '||
v_unit.entertime||', '||
v_unit.exittime ) ;
else
dbms_output.put_line( v_unit.ptname ||', '||
v_unit.unit ||', '||
v_unit.patid ||', '||
v_unit.key ||', '||
v_unit.entertime||', '||
v_unit.exittime ) ;
end if ;
end loop;
end;
/

 

[lewisp]

You cant assign a value to a variable returned in a cursor. Try this:

[tt]declare
cursor c_unit is
select distinct r.patid, r.minorit, r.key, r.name, u.unit, u.entertime , u.exittime
from (select distinct a.patid, a.minorit, a.key, p.name
from doc_table a,
name_table p
where a.patid=p.patid ) r ,
unit_table u
where r.patid=u.patid
and r.key between u.entertime and nvl(u.exittime, sysdate) ;

begin
For v_unit In c_unit loop
if v_unit.key < v_unit.entertime then
-- V_unit.key := v_unit.entertime ;
dbms_output.put_line( v_unit.ptname ||', '||
v_unit.unit ||', '||
v_unit.patid ||', '||
v_unit.entertime ||', '||
v_unit.entertime||', '||
v_unit.exittime ) ;
else
dbms_output.put_line( v_unit.ptname ||', '||
v_unit.unit ||', '||
v_unit.patid ||', '||
v_unit.key ||', '||
v_unit.entertime||', '||
v_unit.exittime ) ;
end if ;
end loop;
end;
/
[/tt]

Or

[tt]declare
l_time DATE;
cursor c_unit is
select distinct r.patid, r.minorit, r.key, r.name, u.unit, u.entertime , u.exittime
from (select distinct a.patid, a.minorit, a.key, p.name
from doc_table a,
name_table p
where a.patid=p.patid ) r ,
unit_table u
where r.patid=u.patid
and r.key between u.entertime and nvl(u.exittime, sysdate) ;

begin
For v_unit In c_unit loop
if v_unit.key < v_unit.entertime then
l_time := v_unit.entertime ;
else
l_time := v_unit.key ;
end if;

dbms_output.put_line( v_unit.ptname ||', '||
v_unit.unit ||', '||
v_unit.patid ||', '||
l_time ||', '||
v_unit.entertime||', '||
v_unit.exittime ) ;
end loop;
end;
/
[/tt]

 

[sem]

Can you explain what you need to do? Update or just display those records?

Regards, Dima

 

[kokiri]

Hi lewisp and Dima,
Thanks for your response. Above code did not capture what I was looking for. Since this key date is before the enter time, this key field should be counted as 28-oct-03 00:00 hrs with 4-SOUTH.
I hope this will clear it.

Thanks.
----------------------
This is how the data is stored in table_doc:
KEY PATID
--------------- ----------
28-oct-03 00:00 117443370

This is unit_table:
ENTERTIME ENTERTIME PATID UNIT
--------------- --------------- ---------- ----------
28-oct-03 00:27 28-oct-03 16:47 117443370 4-SOUTH
28-oct-03 16:47 04-nov-03 10:26 117443370 4-EAST
04-nov-03 10:26 117443370 DISCH