Re: Complicated SQL statement (temporary tables)

If you are using 8.0+ of Oracle, then
look at the Object feature and casting
table-type objects into cursors and back.

There are a couple of examples on
'casting' and 'non-existent data' on
my web site

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Craig Harris wrote in message <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 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 Sat Oct 30 1999 - 08:50:32 CDT