Craig Harris <craigharris_at_my-deja.com> wrote in message
news:7v7a0k$633$1_at_nnrp1.deja.com...
> I have written a SQL query that I would like to try and simplify as I
> am sure there is a better (more efficient way of doing it).
>
> The basic of what I am attempting is to write a query that will join a
> table in my database to a set of data that is generated on the fly by a
> third party application. All of this is being performed via ASP on a
> webserver.
>
> Details:
>
> The following pseudo code describes my process.
>
> Select * from A, B where A_ID=B_ID order by A_SORT_VALUE, B_SORT_VALUE
>
> This is obviously a simple inner join. My problem was that table A does
> not actually exist in the database but is a set of data generated from
> a separate system for the purpose of the current query (concurrent
> users will all have different versions of the table A for each query
> they run).
>
> I get around this problem using the following syntax to generate the
> table A for the purposes of the query.
>
> (select 'AA1' as A_ID, 'AA1_SORT' as A_SORT_VALUE from dual union
> select 'AA2' as A_ID, 'AA2_SORT' as A_SORT_VALUE from dual union
> select 'AA3' as A_ID, 'AA3_SORT' as A_SORT_VALUE from dual)
>
> This code is generated from the data of the third party product and is
> used in place of 'A' in the pseudo code above.
>
> This produces very verbose queries (table A has 600+ records for each
> query) but works and provides reasonable performance.
>
> I want to know if anyone can think of a more elegant solution for this
> problem.
If you don't want to create a physical table, how about using a PL/SQL table
to store your data temporarily. This will be quite quick because it is all
done in memory, although your ordering won't be so easy. If you use a cursor
with a parameter of your A.ID, and a loop in a PL/SQL program this could be
an alternative solution (although not quite as good as populating a physical
table).
Received on Wed Oct 27 1999 - 18:47:30 CDT
