RE: SQL Concepts - Header and Detail

Pete,

This might be a cut and paste issue, but for non-trivial SQL statement, as with other languages,
indenting helps:

Update header
set header.qty = (select sum(qty)
from detail
group by ssn,job,ped
order by ssn,job,ped
)
where exists (select 1
from detail
where header.ssn = detail.ssn,
header.job = detail.job, header.ped = detail.ped
)


Your where clause effects which header records get updated. From the other posts, that's what you
want.

However, SET clause isn't right. You need the following:

Update header HDR
set hdr.qty = (select sum(DTL1.qty)
from detail DTL1
where HDR.ssn = DTL1.ssn
and HDR.job = DLT1.job and HDR.ped = DTL1.ped
)
where exists (select 1
from detail DTL2
where HDR.ssn = DTL2.ssn
and HDR.job = DLT2.job and HDR.ped = DTL2.ped
)


Do you see the difference?
In mine, I'm updating the header record with the sum of all matching detail records.

In yours, you're updating the all the header records with the total sum from all the detail records.

Remember a correlated subquery is simply:
Update header HDR
set HDR.qty = (select sum(DTL1.qty)
from detail DTL1
where HDR.ssn = DTL1.ssn
and HDR.job = DLT1.job and HDR.ped = DTL1.ped
)

That's all you need if:
A) there's at least one detail for every header
or
B) the field being updated in header allows NULLS

If neither of the above are true, you have to add something extra to take care of the header records
without matching detail records. (ie.. The subquery would return NULL)

One way is to use COALESCE to set a "default" value in the header record. The other is to add a where
clause to the update so that you only attempt to update header records that have matching detail.

HTH,
Charles

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Pete Helgren
Sent: Thursday, August 09, 2007 9:59 AM
To: Midrange Systems Technical Discussion
Subject: SQL Concepts - Header and Detail

Seems like I have been doing more of these SQL updates lately
where I have a header file (single instance) with a detail
file (multiple instances). I have a situation where I have a
quantity field in the detail that I want to sum and then
update the header. The summary on the detail is easy:

select ssn,job,ped, sum(qty) from detail group by ssn,job,ped
order by ssn,job,ped

The resulting data set is what I need. So now I need to
relate that to the header which also has the same fields, one
for each instance of ssn,job, and ped (period end date).

Update header set header.qty = (select sum(qty) from detail
group by ssn,job,ped order by ssn,job,ped) **and here is were
I get uncertain ** where exists (select 1 from detail where
header.ssn = detail.ssn, header.job = detail.job, header.ped
= detail.ped)

Is the "where exists" clause the right choice ? Does the
syntax above make sense?

What I am uncertain about is the relationship between the
occurrences in the detail file and how to properly relate
them to: 1. The header records and, 2. the sum of the detail
records that update the header. I am not sure why I have
such an issue with the sub queries that are correlated. Can't
seem to visualize it.

Pete Helgren


--
This is the Midrange Systems Technical Discussion
(MIDRANGE-L) mailing list To post a message email:
MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change
list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting,
please take a moment to review the archives at
http://archive.midrange.com/midrange-l.

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.