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:35:10 GMT
Message-ID: <7v9ftu$nns$1@nnrp1.deja.com>


Hi,

I am not too familiar with PL/SQL as I use a large number of DBMSs and try to stick to straight SQL whereever possible.

The application is web based using ASP and the web server conects all users to the Oracle server using the same user name. This provides problems with namespace clashing (which is why I can't use physical tables).

I was hoping there was a very simple notation I could use.

Obviously this is not available,

Regards,

Craig Harris

In article <7v83mj$e12$4_at_news6.svr.pol.co.uk>,   "-=< a q u a b u b b l e >=-"
<aquabubble_at_Remove.No.Parma.Ham.Remove.geocities.com> wrote:
> Craig Harris <craigharris_at_my-deja.com> wrote in message
> news: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 don't want to create a physical table, how about using a
PL/SQL table
> to store your data temporarily. This will be quite quick because it
is all
> done in memory, although your ordering won't be so easy. If you use a
cursor
> with a parameter of your A.ID, and a loop in a PL/SQL program this
could be
> an alternative solution (although not quite as good as populating a
physical
> table).
>
>

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

Original text of this message

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