Looping over broken range

[wwlos]

Hi,

In PL/SQL (Oracle 9i) I need to iterate over a discontinuous range of patient-ID's (numbers).
I need something like

WHILE variable IN (SELECT DISTINCT ....)
LOOP...

However Oracle does not allow using a subquery in this way.
I know in version 11 there is the CONTINUE construct but we do not run 11...
Does anyone have suggestions on how to do this in an other way?

thanks,
Willem

[amitpatel66]

Do you mean patient_id can be 1,3,5,7,8,10,11 and so on and not continuous??

[wwlos]

Do you mean patient_id can be 1,3,5,7,8,10,11 and so on and not continuous??

Yes, indeed, that's it.
Willem

[amitpatel66]

Yes, indeed, that's it.
Willem

Try this:

 
SQL> SET SEVRVEROUTPUT ON
 
SQL>declare
cursor C1 is SELECT patient_id FROM table_name;
BEGIN
FOR I IN C1 LOOP
DBMS_OUTPUT.PUT_LINE(I.patient_id);
END LOOP;
END;
/
 
 

[wwlos]

Thanks. I tried the cursor loop before, but I could not get it to work.
The code below does exactly what I want... as long as there is a continuous patient range (Current_pat). As soon as there is a gap, processing stops and I get the "no data found" message.
Maybe this is getting too specific to be discussed here.. in which case I humbly apologize.

Thanks again,
Willem

The rest is...

 
declare
TYPE rec_data IS RECORD
(as11 number, as12 number, as13 number, as14 number,
as21 number, as22 number, as23 number, as24 number,
as31 number, as32 number, as33 number, as34 number
);
gegs rec_data;
First_Pat number;
Last_Pat number;
Current_Pat number;
teller number;
 
begin
select min(seq_med_diagnose) into First_Pat from med_diagnose_as;
select max(seq_med_diagnose) into Last_Pat from med_diagnose_as;
 
Current_pat:= First_Pat;
 
while Current_pat between First_Pat and Last_Pat
loop
 
select * into gegs
from (select *
from (select nvl(seq_alg_dsm_iv , 99999) "as11"
, nvl(lead(seq_alg_dsm_iv,1) over (order by seq_MED_diagnose_as), 99999) "as12"
, nvl(lead(seq_alg_dsm_iv,2) over (order by seq_MED_diagnose_as), 99999) "as13"
, nvl(lead(seq_alg_dsm_iv,3) over (order by seq_MED_diagnose_as), 99999) "as14"
from med_diagnose_as
where seq_med_diagnose = Current_Pat
and hc_as = 1
and rownum between 1 and 4
)
where rownum=1
)
, (select *
from (select nvl(seq_alg_dsm_iv , 99999) "as21"
, nvl(lead(seq_alg_dsm_iv,1) over (order by seq_MED_diagnose_as), 99999) "as22"
, nvl(lead(seq_alg_dsm_iv,2) over (order by seq_MED_diagnose_as), 99999) "as23"
, nvl(lead(seq_alg_dsm_iv,3) over (order by seq_MED_diagnose_as), 99999) "as24"
from med_diagnose_as
where seq_med_diagnose = Current_Pat
and hc_as = 2
and rownum between 1 and 4
)
where rownum=1
)
, (select *
from (select nvl(seq_alg_dsm_iv , 99999) "as31"
, nvl(lead(seq_alg_dsm_iv,1) over (order by seq_MED_diagnose_as), 99999) "as32"
, nvl(lead(seq_alg_dsm_iv,2) over (order by seq_MED_diagnose_as), 99999) "as33"
, nvl(lead(seq_alg_dsm_iv,3) over (order by seq_MED_diagnose_as), 99999) "as34"
from med_diagnose_as
where seq_med_diagnose = Current_Pat
and hc_as = 3
and rownum between 1 and 4
)
where rownum=1
)
;
 
Current_pat:= Current_pat+1;
 
dbms_output.put_line(Current_Pat || ',' || gegs.as11 || ',' || gegs.as11 || ',' || gegs.as12 || ',' || gegs.as13 || ',' || gegs.as14 || ',' ||
gegs.as21 || ',' || gegs.as22 || ',' || gegs.as23 || ',' || gegs.as24 || ','||
gegs.as31 || ',' || gegs.as32 || ',' || gegs.as33 || ',' || gegs.as34 );
end loop;
end;
 

[amitpatel66]

Ofcourse that will happen becuase you are taking MIN and MAX values and if any value between MIN and MAX does not exist then it will result in NO DATA FOUND. You need to reimplement your code using cursor that way I showed you in my previous post. Try and post back what error you get using Cursor

[wwlos]

Hi,

Sorry for not responding until now. I have been ill for a while.
The problem has been solved by our application provider in the latest release. I haven't got the time to work on this problem any longer.

Thank you for your help,
Regards,
Willem

[amitpatel66]

Hi,

Sorry for not responding until now. I have been ill for a while.
The problem has been solved by our application provider in the latest release. I haven't got the time to work on this problem any longer.

Thank you for your help,
Regards,
Willem

Thats good that problem is resolved. ANyways, do post back when ever you have any further issues.