Re: SQL Concepts - Header and Detail

[Pete Helgren <Pete@xxxxxxxxxx>]

Thanks for the examples.  I hadn't give thought to an existing header 
with no detail.  Something to think about.  Your coalesce is what Joe 
mentioned as well.  You are saying without it, the header in your 
example would have remained at 5 and 7 ? I get that.

Your update is a bit more compact but looks similar to what I put 
together. 

Orphan records are a problem in this case (I just found that out).  But, 
in that case I would do an insert into the header where the records do 
exists in the detail but don't exist in the header.  I think I already 
have been there (and checked for duplicates).

Thanks,

Pete

rob@xxxxxxxxx wrote:
> The only caveat is what to do with a header with no detail.
> Do you set qty to zeros or do you not update it?
>
> select * from qtemp/header
> ....+....1....+....2....+....3....+
> MYKEY1  MYKEY2                  QTY
>   A       A                       5
>   A       2                       6
>   B       1                       7
> ********  End of data  ******** 
>
> select * from qtemp/detail
> ....+....1....+....2....+....3....+....4....+....5..
> MYKEY1  MYKEY2          TRAN#                   QTY 
>   A       2                 5                    27 
>   A       2                 6                    45 
> ********  End of data  ******** 
>
> UPDATE QTEMP/HEADER 
> SET QTY = COALESCE( 
>  (SELECT SUM(QTY) FROM QTEMP/DETAIL 
>   WHERE HEADER.MYKEY1=DETAIL.MYKEY1 AND HEADER.MYKEY2=DETAIL.MYKEY2)
>  , 0) 
>
> select * from qtemp/header
> ....+....1....+....2....+....3....+
> MYKEY1  MYKEY2                  QTY
>   A       A                       0
>   A       2                      72
>   B       1                       0
> ********  End of data  ******** 
>
> If you have orpan detail records and you need to insert those into header, 
> that's a different story.
>
>
> Rob Berendt
>