JON wrote:
>
> Peter,
> Thanks for the reply. First, I made a mistake in my example
> in not including
> a field from the tblAOType in the output that would've shown
> a need to use
> that table. Aside from that, I had already tried what you
> suggested before
> writing with no change in the results. Here's a better example of the
> offending query with the most restricting condition first. (Using the
> Explain command renders that same result as before).
Just to point out:
changing the sequence of conditions will not change the number of
(intermediate) resultrows produced --> it is not astonishing to see no change
in the behaviour.
And to build a view and to do the select on top of this view is an
idea, but none of us should believe that this will make things better
(it will make things even worse, I assume).
How many records are involved in these three tables and
how 'full' is your database?
Elke
SAP Labs Berlin
>
> SELECT
> "OCPC"."TBLAOTYPE"."AOTYPE","OCPC"."TBLARCHOWNER"."NAME","OCPC
> "."TBLPROJAO".
> "PROJAO","OCPC"."TBLPROJAO"."SHOWONRESULTS","OCPC"."TBLARCHOWN
> ER"."PHONE","O
> CPC"."TBLARCHOWNER"."ZIP","OCPC"."TBLARCHOWNER"."STATE","OCPC"
> ."TBLARCHOWNER
> "."CITY","OCPC"."TBLARCHOWNER"."ADDR1"
>
> FROM "OCPC"."TBLAOTYPE","OCPC"."TBLARCHOWNER","OCPC"."TBLPROJAO"
>
> WHERE (("OCPC"."TBLPROJAO"."PROJID" = 8329) AND
> ("OCPC"."TBLPROJAO"."SHOWONRESULTS" = TRUE)) AND
> "OCPC"."TBLARCHOWNER"."ID"=
> "OCPC"."TBLPROJAO"."ARCHOWNERID" (+) AND
> "OCPC"."TBLAOTYPE"."AOTYPEID"=
> "OCPC"."TBLPROJAO"."AOTYPEID" (+)
>
> ORDER BY "OCPC"."TBLPROJAO"."PROJAO" ASC
>
> This is just one example of a query I build on the fly in
> Visual Basic code.
> Building views seems unreasonable and it doesn't seem to me
> that there are
> THAT many records involved here. But if I'm mistaken then by all means
> somebody straighten me out. :)
>
> Thanks,
> Jon
>
>
>
> "Peter Willadt" <[EMAIL PROTECTED]> wrote in message
> [EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> > Hello,
> > On Mittwoch, 19. Februar 2003 19:20, JON wrote:
> > >
> > > I have the following query created with the visual query tool:
> > > SELECT
> > >
> "OCPC"."TBLARCHOWNER"."NAME","OCPC"."TBLPROJAO"."SHOWONRESULTS
> ","OCPC"."TBL
> > >A
> > >
> RCHOWNER"."PHONE","OCPC"."TBLARCHOWNER"."ZIP","OCPC"."TBLARCHO
> WNER"."STATE"
> > >, "OCPC"."TBLARCHOWNER"."CITY","OCPC"."TBLARCHOWNER"."ADDR1" FROM
> > > "OCPC"."TBLAOTYPE","OCPC"."TBLARCHOWNER","OCPC"."TBLPROJAO" WHERE
> > > "OCPC"."TBLARCHOWNER"."ID"=
> "OCPC"."TBLPROJAO"."ARCHOWNERID" (+) AND
> > > "OCPC"."TBLAOTYPE"."AOTYPEID"=
> "OCPC"."TBLPROJAO"."AOTYPEID" (+) AND
> > > ((tblProjAO.ShowOnResults)=TRUE) and tblProjAO.ProjID =
> 8329 order by
> > > ProjAO When I execute the query I get the Space for results table
> exhausted
> > > error. I'm using kernal 7.3.0, SQL Studio 7.4.3.6, ODBC driver
> > > 7.04.03.00.
> > >
> > exhausting space for results mostly comes from building cartesian
> products.
> > But in this case, it doesn't look like that.
> > Generally, it might be goos to put the most restricting
> conditions first
> (in
> > this case the last both) or to build a view resulting on
> these conditions
> and
> > have the query use this view instead of the underlying base
> table(s).
> >
> > In this particular case I wonder what reason there is to
> include TBLAOTYPE
> in
> > the query. It seems not to restirct the result set nor to
> appear in the
> > output. I also wonder where the ProjAO in the order by
> clause comes from.
> >
> > Perhaps you might try rewriting your query like this and try again.
> >
> > SELECT
> > TBLARCHOWNER.NAME,TBLPROJAO.SHOWONRESULTS,TBLARCHOWNER.PHONE,
> > TBLARCHOWNER.ZIP,TBLARCHOWNER.STATE,
> > TBLARCHOWNER.CITY,TBLARCHOWNER.ADDR1 FROM
> > TBLARCHOWNER,
> > TBLPROJAO
> > WHERE tblProjAO.ShowOnResults=TRUE
> > and tblProjAO.ProjID = 8329
> > and TBLARCHOWNER.ID= TBLPROJAO.ARCHOWNERID (+)
> > order by ProjAO
> >
> > Peter Willadt
>
>
>
> _______________________________________________
> sapdb.general mailing list
> [EMAIL PROTECTED]
> http://listserv.sap.com/mailman/listinfo/sapdb.general
>
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
