Thanks for your prompt answer, but: SELECT a.IDMandant,a.PRIMANOTA, a.dtmPeriode, a.dtmBDatum, d.IDKTO, e.IDKTO AS IDKTOG, a.IDKonto, a.IDKontoG, a.intSSC, sum(a.lngSoll) as slngSoll, sum(a.lngHaben) as slngHaben, a.strBelegNr, a.IDM, a.strBuchungsText
FROM refKonten d,tblBuchungen a,refKonten e where (d.IDKonto = a.IDKontoG and a.IDKonto = e.IDKonto) and (a.IDKonto = 103) Or (a.IDKontoG = 103) and ((a.dtmPeriode >=01-03-2003 And a.dtmPeriode < 01-04-2003)) and (a.IDMandant=1) GROUP BY a.IDMandant,a.PRIMANOTA, a.dtmPeriode, a.dtmBDatum, d.IDKTO, e.IDKTO, a.IDKonto, a.IDKontoG, a.intSSC, a.strBelegNr, a.IDM, a.strBuchungsText ORDER BY a.PRIMANOTA fails with the same error. I've understand, that HAVING is filterig the ready resultset, and most of data is then useless. But the error occurs also without having clause. The original of my application runs in moment with JET per ADO as database system. I like to upgrade it so that small system can run with mdb's and large systems can run with sap. I hope the upsizing isn't to difficult. bye Hagen "Zabach, Elke" <[EMAIL PROTECTED]> schrieb im Newsbeitrag news:[EMAIL PROTECTED] > Hagen Schl�merwrote: > > > > > I have the following problem in executing a sql-statement: > > > > SQL-Statement > > > > SELECT a.IDMandant,a.PRIMANOTA, a.dtmPeriode, a.dtmBDatum, > > d.IDKTO, e.IDKTO > > AS IDKTOG, a.IDKonto, a.IDKontoG, a.intSSC, sum(a.lngSoll) as > > slngSoll, > > sum(a.lngHaben) as slngHaben, a.strBelegNr, a.IDM, a.strBuchungsText > > > > FROM refKonten d,tblBuchungen a,refKonten e > > > > where > > > > (d.IDKonto = a.IDKontoG and a.IDKonto = e.IDKonto) > > > > GROUP BY a.IDMandant,a.PRIMANOTA, a.dtmPeriode, a.dtmBDatum, d.IDKTO, > > e.IDKTO, a.IDKonto, a.IDKontoG, a.intSSC, a.strBelegNr, a.IDM, > > a.strBuchungsText > > > > HAVING a.IDMandant=1 and (a.dtmPeriode >= 01-03-2003 And > > a.dtmPeriode < > > 01-04-2003) and (a.IDKonto = 12) Or (a.IDKontoG = 12) > > > > > > > > ..shows the following error: > > > > General error;-904 POS(1) Space for result tables exhausted. > > > > There are 200000 records in tblBuchungen, the original > > statemant was created > > with access (with inner join statements), i have changed it to > > where-clauses. In the mailing-list there are some answers to > > "space for > > result tables exhausted" but i have no idea what i can do. > > > Usually the WHERE-clause is used to check conditions > on single columns or the join-conditions and the HAVING-clause > is used only for checking the result of the groups, for example > 'only those groups with more than 8 members', > 'only that customer having bought for more than (summed up) 100 000 Euro'. > > You prepare a whole bunch of groups for > ALL mandants and > ALL periods and > ALL Konto-Nos and > ALL.... > > and in the end (if not stopped by -904) you will say: > ok, I just want the result for exactly one mandant, one of two Konto > within a small period and the other 99% of the intermediately prepared > group-results can be thrown away. > > As I said, usually for space and performance reasons such conditions > as you used in the HAVING-clause have to be in the WHERE-clause. > Try that and you will succeed. > > Elke > SAP Labs Berlin _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
