Avoiding Temporary Tables

Hello All,
I have a question about how I might avoid using a temporary table in a sql
query. In a query I am writing I am trying to extract data from a table o
n
an existing database via a set of identifier codes (These queries are writte
n
from the Matlab environment and the list of identifiers are easily accesible
in matlab).
The query I am currently using looks something like this...
select t.var1, t.code, t.var2
from pinf t
join
( SELECT distinct s.pcode as code
FROM prc s
WHERE s.code in (74156, 54471) ) -- identifiers 74156, 54471
as prctable on prctable.code = t.code
and '12/06/2005' >= t.dt1 and t.d2 <= '12/06/2005'
with the identifier being 74156, 54471. The problem is there is not a
convenient table with all of the identifiers in it. So the solution above
where I draw from an existing table is not sufficient.
I believe that I can create a temporary table with these identifiers in it,
but the performance of the query slows markedly.
Is there some other way to extract the data with these identifier from the
table pinf without creating a temporary table?
Any help would be greatly appreciated.
Cheers,
Lanny>> ... with the identifier being 74156, 54471.
Is this a set of two identifier values or a single comma separated
identifier value with two numbers in them?
I am not familiar with the matlab environment, but how exactly do you
extract the identifiers to the #temp table? The general alternatives ( which
in many cases may not be sufficient ) are using a view or even a base table
which can have all the required identifiers.
Anith|||It is a set of 2 distinct identifier, and conceptually I would want to look
for many more than 2 (perhaps on the order of thousands) of these distinct
identifiers.
I extract the identifiers via another query that I know functions properly
and will give me the desired identifier. I looked into using a base table
or a view, and did not believe they provided the functionality I needed.
Essentially, I have some list of numbers and I want to extract that list, bu
t
all of the elements of this list do not exist on any distinct table in the
database.
Thanks so much for the help.
"Anith Sen" wrote:

> Is this a set of two identifier values or a single comma separated
> identifier value with two numbers in them?
>
> I am not familiar with the matlab environment, but how exactly do you
> extract the identifiers to the #temp table? The general alternatives ( whi
ch
> in many cases may not be sufficient ) are using a view or even a base tabl
e
> which can have all the required identifiers.
> --
> Anith
>
>|||On Wed, 7 Dec 2005 10:18:03 -0800, Lanny wrote:

>It is a set of 2 distinct identifier, and conceptually I would want to look
>for many more than 2 (perhaps on the order of thousands) of these distinct
>identifiers.
>I extract the identifiers via another query that I know functions properly
>and will give me the desired identifier. I looked into using a base table
>or a view, and did not believe they provided the functionality I needed.
>Essentially, I have some list of numbers and I want to extract that list, b
ut
>all of the elements of this list do not exist on any distinct table in the
>database.
>Thanks so much for the help.
Hi Lanny,
I'm not sure if I understand your requirements completely, but based on
what I do understand, you might find what you need on Erlands site:
http://www.sommarskog.se/arrays-in-sql.html
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)