>Sir I do not know where I am using GROUP BY in both parts of the UNION in the inner subquery.
>
>Please modify.

Tariq, please go back and take a very close look at your original SQL code that you posted. It had two GROUP BY statements on the inside subquery. The more recent code that you posted did not.

Regardless, let's look at this code, that's part of the UNION, from your most recent post.


SELECT distinct(acc_code) as acc_code ,max(acc_name) as acc_name, qty, cr_amount
from crsalp where substring(acc_code,1,2)='32' and type='P'


You're asking for an aggregation (max) of the acc_name, and you're also specifying other columns (acc_code, qty, cr_amount). When you aggregate a column, and you specify other columns in the SELECT without aggregating on them, you need to specify a GROUP BY.

Now...if your query is going against FoxPro tables, you *might* want to try adjusting the ENGINEBEHAVIOR to tap into older Fox behavior for aggregations, though I generally think it's better to make the syntax work with propery aggregation rules.