RE: Space for results table exhausted

[Holger Becker]

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

It's a known problem with outer joins in SAP DB and we are working on this topic. 
(I know I wrote this already several times, sorry for the delay)

Because of some problems with outer joins between more than two tables 
in the past we decide to execute these joins in the order given by the 
order of the tables in the from clause.

In your example the join will be executed in order "TBLAOTYPE" -> "TBLARCHOWNER" ->
"TBLPROJAO".

But this might not be the best plan and huge temporary results might be created.

As a workaround you could change the table sequenece in the from clause.

BTW in your example the outer join seems to make no sense because the local predicates
will remove every outer row from the result.

HTH.

Kind regards,
Holger
SAP Labs Berlin