Home » SQL & PL/SQL » SQL & PL/SQL » Array, temp table, loop? Suggestions please.
Array, temp table, loop? Suggestions please. [message #207990] Thu, 07 December 2006 12:57 Go to next message
Eilistraee
Messages: 5
Registered: September 2006
Junior Member
I have a stored procedure that I need to repeat for every item in a list. I could get the results of each individual query and insert them into a temp table (I think, I haven't tried inserting to a temp table before), and that's tentatively my plan. But I don't want to have to write the same block of code 20 times with one character different each time - especially because this code will have several if statements to decide which subsection runs.

Basically, I have these lines:
  • select vaccine_id from dba.vaccine_antigen_xref where antigen_id = 4
  • select vaccine_id from dba.vaccine_antigen_xref where antigen_id = 22
  • select vaccine_id from dba.vaccine_antigen_xref where antigen_id = 10 or antigen_id = 56


and so on down the list and I need to run the procedure over again for each of these values to get my results and I need the results from all of the queries together in a table

select (select distinct count (distinct(p.patient_id))
from dba.patient p, 
.
.
.
where 
.
.
.
and round((dba.vaccine_history.vaccination_date - p.dob)/ 30.4, 2) between 0 and 3.99)
and dba.vaccine_history.vaccine_Id in (SQL STATEMENT ABOVE);


I'd love to be able to do this same thing with the age ranges (between 0 and 3.99 in this example) as well, but I'd be thrilled if there was just a way to do this.

[Updated on: Thu, 07 December 2006 13:19]

Report message to a moderator

Re: Array, temp table, loop? Suggestions please. [message #208031 is a reply to message #207990] Thu, 07 December 2006 20:33 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I suspect you are protecting us from some of the complexity, because based on your description of the problem there is no reason why you couldn't just:

select (select distinct count (distinct(p.patient_id))
from dba.patient p, 
.
where 
.
and round((dba.vaccine_history.vaccination_date - p.dob)/ 30.4, 2) between 0 and 3.99)
and dba.vaccine_history.vaccine_Id in (
    select vaccine_id 
    from dba.vaccine_antigen_xref 
    where antigen_id IN (4,22,10,56,....)
);


If the procedure contains conditional/looping logic that you are not showing, you could convert it into a PIPELINED FUNCTION (search the doco) which would allow you to reference it directly in your SQL statement like so:
select (select distinct count (distinct(p.patient_id))
from dba.patient p, 
.
where 
.
and round((dba.vaccine_history.vaccination_date - p.dob)/ 30.4, 2) between 0 and 3.99)
and dba.vaccine_history.vaccine_Id in (
    select column_value 
    from TABLE(my_pipelined_func())
);


Ross Leishman
Re: Array, temp table, loop? Suggestions please. [message #208178 is a reply to message #208031] Fri, 08 December 2006 07:56 Go to previous message
Eilistraee
Messages: 5
Registered: September 2006
Junior Member
Thanks, I'll look up the pipelined function.

The reason I can't do as you described is because I need the count for each antigen indivually, and not the count for all antigens together. Basically, how many people have gotten their HepB vaccination by three months of age, how many have gotten their Polio vaccination by three months of age, etc. for each antigen and age reflected on the report.
Previous Topic: search redundant record
Next Topic: Using INSTR function
Goto Forum:
  


Current Time: Thu Dec 08 10:26:51 CST 2016

Total time taken to generate the page: 0.11828 seconds