Hi Nick,
>>>
>>>I would suggest to break this SQL down to several SQLs and see where exactly you stop getting the data. Then you may try to combine it back again (or not).
>>>
>>>Nick
>>
>>
>>This is my test:
>>
>>

>>SELECT * FROM tranmstr ;
>>left JOIN misclndr on tranmstr.tranid = misclndr.tranid ;
>>inner JOIN propmstr on tranmstr.propid = propmstr.propid ;
>>inner JOIN sitemstr on tranmstr.propid = sitemstr.propid  and sitemstr.town="GOSN";
>>left JOIN bldgmstr on tranmstr.propid = bldgmstr.propid ;
>>where (prefcode = "P"  and SOURCE="S") ;
>>and between(extrYear+extrWeek ,'200004', '200004') ;
>>into cursor tttt
>>
>>
>>** The first SQL produced 0 records (there is no transactions for this town)
>>
>>select * from tttt right join towns on tttt.town_a=towns.town where towns.town='GOSN'
>>
>>** The second SQL produced one record (as I desire) with NULL in all tttt fields and Towns fields for this town.
>>

>>
>>So, now is the question: how can I combine this 2 SQLs into one and get the same result? Is it possible without joing with Towns table? (I don't need an extra information from Towns)
>>
>>Thanks in advance.
>
>So, if it produces the result you need, why not just leave it as 2 SQLs?

I can't :( I need to do it in one SQL, otherwise I (or our team) need to redesign several applications, which we'd prefer not to.
Ok, let me explain. I have 4 separate applications:
Query, BtCrit, XForm and Reporter.
BtCrit allows me to specify criteria (my colleague started it, then I finished). It returns where expression in Unload method, like, for example,
INLIST(SiteMstr.town,'BROO','CAMB') and price<10000 and sellname='Y'.
It also returns Join Condition through variable (public :(). That's it.
The Query application executes query. I haven't looked into this application carefully. This application is undependable on BtCrit.

XForm transforms 'row data' into desired output format.
Reporter generates Report (or COPY TO SDF).

As you can see from this scheme, I need to tell Query where criteria and Join condition from BTCrit and then let Query do its job. I can not split SQL into two pieces.

On Friday and today I tried different tests without any luck. I guess, it can not be done in one SQL. I think we need to decide, how else can we handle this situation.

Thanks for your help.