Re: Complicated SQL statement (temporary tables)

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
> 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.

The only other thing I can think of to simplify this (and maybe speed it up) is to change the UNIONs to UNION ALLs since this will not require any sorting. Also, the only time you need an alias is in the first SELECT statement in a bunch of UNIONs.

I did try messing about with concatenating strings with ROWNUMs to get your 'AA1', 'AA2', etc. but I didn't get far... I'm sure it must be possible to get your list in just one query (if that's what you want).

Cheers...

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Oct 28 1999 - 08:40:06 CDT