>Hi All:
>
>I need some help with the following SQL code. I can do what I want in more than
>one select statement, but my goal is the most elegant solution.
>
>Consider the two follwing tables:
>
>
>Table EGGS
>Record# ED_INVNUM ED_LOADNUM ED_BATNUM ED_CASES
> 1 432059 293 368 29.34
> 2 432059 293 376 146.70
> 3 432059 293 391 29.34
> 4 432059 293 398 123.70
> 5 432059 293 398 23.00
> 6 432059 293 399 48.00
> 7 432059 293 412 40.00
> 8 432059 293 415 58.00
>
>Table TCHICKS
>Record# HI_INVNUM HI_LOADNUM HI_BATNUM HI_AMT
> 1 A0017 293 368 9101
> 2 A0017 293 376 42413
> 3 A0018 293 391 7826
> 4 A0018 293 398 42174
> 5 A0018 293 398 4280
> 6 A0018 293 399 15555
> 7 A0018 293 412 9649
> 8 A0018 293 415 15516
> 9 A0018 293 412 2000
> 10 A0018 293 412 702
>
>
>
>I then run the followng SQL code:
>
>
>SELECT eggs.ed_invnum, eggs.ed_loadnum, ;
> eggs.ed_batnum, SUM(eggs.ed_cases * 360) as eggs, tchicks.hi_invnum, tchicks.hi_batnum, ;
> SUM(tchicks.hi_amt) as chicks ;
> FROM ;
> eggs ;
> INNER JOIN tchicks ;
> ON eggs.ed_loadnum = tchicks.hi_loadnum;
> AND eggs.ed_batnum = tchicks.hi_batnum;
> GROUP BY eggs.ed_invnum, eggs.ed_invdat, eggs.ed_loadnum, eggs.ed_batnum, tchicks.hi_invnum, tchicks.hi_batnum ;
> INTO CURSOR result
>
>
>And get the following result:
>
>
> ED_INVNUM ED_LOADNUM ED_BATNUM EGGS HI_INVNUM HI_BATNUM CHICKS
> 432059 293 368 10562.40 A0017 368 9101
> 432059 293 376 52812.00 A0017 376 42413
> 432059 293 391 10562.40 A0018 391 7826
> 432059 293 398 105624.00 A0018 398 92908
> 432059 293 399 17280.00 A0018 399 15555
> 432059 293 412 43200.00 A0018 412 12351
> 432059 293 415 20880.00 A0018 415 15516
>
>
>Which is obviously wrong, since there should be 46,454 chicks in hi_batnum 398, not 92,908. What the system is doing is SUMming four records instead of two. What's the right way to code this?
>
>Thanks,
>
>Yossi
Data is incomplete for me.
SELECT *;
FROM (SELECT ed_invnum, ed_loadnum, ed_batnum, SUM(ed_cases)*360 eggs FROM eggs GROUP BY 1,2,3) eggs;
INNER JOIN ;
(SELECT hi_invnum, ed_loadnum, hi_batnum, SUM(hi_amt) chicks FROM tchicks GROUP BY 1,2,3) tchicks
ON eggs.ed_loadnum = tchicks.hi_loadnum;
AND eggs.ed_batnum = tchicks.hi_batnum;
INTO CURSOR result
