Re: Complicated SQL statement (temporary tables)

Hi.

 Is it possible to create table A as
 CREATE TABLE A (
   UNAME VARCHAR2(30),
   A_ID ...,
   A_SORT ... );  And

 SELECT * FROM A,B
 WHERE A.UNAME = USER AND A_ID = B_ID;  HTH. Michael.

In article <7v7a0k$633$1_at_nnrp1.deja.com>,   Craig Harris <craigharris_at_my-deja.com> wrote:
> 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
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Oct 27 1999 - 16:12:46 CDT