That worked! Thanks. So far I have JOINED 8 of those derived tables. How many can I do?
Thanks again,
Yossi
>Not necessary. Here's an alternative that uses derived table (not tested)
>
>SELECT hi_flcode, SUM(hi_amt), ;
> SumWldelvf, SumHddelvf ;
> FROM hatcinfo ;
> JOIN ( ;
> SELECT lm_flcode, SUM(lm_wldelvf) AS SumWldelvf, ;
> SUM(lm_hddelvf) AS SumHddelvf FROM lotmast ;
> WHERE SUBSTR(lm_flcode,5,4) <= lcFlockNumberEnd ;
> AND SUBSTR(lm_flcode,5,4) >= lcFlockNumberBegin ;
> GROUP BY lm_flcode ;
> ) lm ON lm.lm_flcode = hi_flcode ;
> WHERE SUBSTR(hi_flcode,5,4) <= lcFlockNumberEnd ;
> AND SUBSTR(hi_flcode,5,4) >= lcFlockNumberBegin ;
> GROUP BY hi_flcode ;
> INTO CURSOR results
>
>>Thanks, Sergey. I guaess that means that I need to execute a subquery for each field I want.
>>
>>>A subquery in a field list can return only 1 value
>>>
...
>>> (SELECT SUM(lm_wldelvf) FROM lotmast ;
>>> WHERE lm_flcode = hi_flcode ) AS sum1, ;
>>> (SELECT SUM(lm_hddelvf) FROM lotmast ;
>>> WHERE lm_flcode = hi_flcode ) AS Sum2 ;
>>>...
>>>>