>>I have a SQL query as follows:
>>
>>Select q.*, a.rsc, a.eor, a.wgc, '2P + DE - DF' as Lrdt,;
>> Sum(a.amt)+ Sum(b.amt)- Sum(c.amt) as Lcol,;
>> d.rdt as Rrdt,;
>> Sum(d.amt) as RCol;
>>From Qlp-All q;
>>Left Outer Join EndofYear a On a.verlvlkey = q.verlvlkey+'2P'+ q.recordtype;
>>Left Outer Join Reimbursements b On b.verlvlkey = q.verlvlkey+'DE'+ q.recordtype;
>>Left Outer Join Reimbursements c On c.verlvlkey = q.verlvlkey+'DF'+ q.recordtype;
>>Left Outer Join EndofPeriod d On d.verlvlkey = q.verlvlkey+'DH'+ q.recordtype;
>>Where (a.RDT = '2P' or b.RDT = 'DE' or c.RDT = 'DF' or d.RDT = 'DH') and !q.OA = '00';
>>Having q.RecordType = 'DM';
>>Group By q.verlvlKey
>>
>>
>>When I run the query, I get the message:
>>
>>Program Error: File c:\document\locals\temp\00007imh002o.tmp is too large
>>
>>Which leads me to believe that for some reason it is hitting the 2 GB barrier on creating the resulting set when the query is run.
>>I have already tried different things, like eliminating the Where clause and putting the conditions in the join statements, deleted all the tags and recreate them and it still gives me an error. The number of records I am pulling is about 50,000, so the resulting size shouldn't be an issue.
>>I have plenty of swap space on the disk, but I am not sure if this is hardware or query related.
>>I have other SQl statements thst work fine, but this is te only one that uses the reimbursement table twice for calculations.
>>I would like to leave this as 1 query and not break into 2 queries if possible.
>>I open to suggestions and will try different options, and different ideas are encouraged. I need to understand why this query doesn't work when ones similar to it work fine.
>>thanks,
>>Alp
>
>Having clause is not used this way. Anyway I would do it differently:
>

>Select q.*, a.rsc, a.eor, a.wgc, '2P + DE - DF' as Lrdt,; 
>       aamt+ bamt- camt as Lcol,; // Need to add NVL here too
>       d.rdt as Rrdt,;  ?? 'DH' always or NULL - do we want to get NULL  too ?
>       Sum(d.amt) as RCol; 
>From (select * from Qlp-All where recordType='DM') q; 
>Left Outer Join (select eor, wgc, verlvlkey, sum(*) as aAmt from EndofYear where RDT = '2P' group by 1,2,3) a On a.verlvlkey = q.verlvlkey+'2P'+ q.recordtype;
>Left Outer Join (select verlvlkey, sum(*) as bAmt from Reimbursements where RDT = 'DE'  group by 1) b On b.verlvlkey = q.verlvlkey+'DE'+ q.recordtype;
>Left Outer Join (select verlvlkey, sum(*) as cAmt from Reimbursements where RDT = 'DF'  group by 1) c On c.verlvlkey = q.verlvlkey+'DF'+ q.recordtype;
?????????? Why do we need the last join if we only get one RDT and one column in the result
>Left Outer Join (select verlvlkey, rdt from EndofPeriod where RDT = 'DH') d On d.verlvlkey = q.verlvlkey+'DH'+ q.recordtype;
>

>
>Also, using a,b,c,d ... (intil k, I believe) is not a good idea since VFP reserved them as designations for workareas.
>Good Luck

I added some comments based on Yuri's code - see if this is close to what you had in mind.