Hi Fabio:
Thank you; this is one step on my road to SQL excellence!
Yossi
>>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
>
