Complicated SQL statement (temporary tables)

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 need any more information please do not hesitate to contact me.

Regards,

Craig Harris
Senior Consultant
Professional Services
Software Spectrum UK Ltd

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Oct 27 1999 - 11:41:57 CDT