Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Complicated SQL statement (temporary tables)

Re: Complicated SQL statement (temporary tables)

From: Craig Harris <craigharris_at_my-deja.com>
Date: Thu, 28 Oct 1999 12:29:56 GMT
Message-ID: <7v9fk1$nj3$1@nnrp1.deja.com>


Hi,

I do not think a physical table is the way to go as the application is ASP (web based) with a number of concurrent users who all access the Oracle system through the ASP that uses a single user account. Ensuring physical table names do not clash will be a pain and I am not sure if it would provide the same performance I am already getting.

Regards,

Craig Harris

In article <7v7psb$iic$1_at_nnrp1.deja.com>,   michael_bialik_at_my-deja.com wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Oct 28 1999 - 07:29:56 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US