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:

[code=oracle]

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;
/

[/code]

[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...

[CODE=oracle]

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_dia gnose) into First_Pat from med_diagnose_as ;
select max(seq_med_dia gnose) 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_al g_dsm_iv,1) over (order by seq_MED_diagnos e_as), 99999) "as12"
, nvl(lead(seq_al g_dsm_iv,2) over (order by seq_MED_diagnos e_as), 99999) "as13"
, nvl(lead(seq_al g_dsm_iv,3) over (order by seq_MED_diagnos e_as), 99999) "as14"
from med_diagnose_as
where seq_med_diagnos e = 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_al g_dsm_iv,1) over (order by seq_MED_diagnos e_as), 99999) "as22"
, nvl(lead(seq_al g_dsm_iv,2) over (order by seq_MED_diagnos e_as), 99999) "as23"
, nvl(lead(seq_al g_dsm_iv,3) over (order by seq_MED_diagnos e_as), 99999) "as24"
from med_diagnose_as
where seq_med_diagnos e = 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_al g_dsm_iv,1) over (order by seq_MED_diagnos e_as), 99999) "as32"
, nvl(lead(seq_al g_dsm_iv,2) over (order by seq_MED_diagnos e_as), 99999) "as33"
, nvl(lead(seq_al g_dsm_iv,3) over (order by seq_MED_diagnos e_as), 99999) "as34"
from med_diagnose_as
where seq_med_diagnos e = 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_P at || ',' || 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;
[/code]

[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.