> Steve,
> That was just one shot. if you're using SQL Server you can do something
> like this too
> update MYHEADER
> set HDR_VAL1 = (select sum(DET_VAL1) from MYDETAIL where DET_HDR =
> h.HDR_KEY),
> HDR_VAL2 = (select sum(DET_VAL2) from MYDETAIL where DET_HDR =
> h.HDR_KEY),
> HDR_VAL3 = (select sum(DET_VAL3) from MYDETAIL where DET_HDR =
> h.HDR_KEY),
> HDR_VAL4 = (select sum(DET_VAL4) from MYDETAIL where DET_HDR =
> h.HDR_KEY),
> HDR_VAL5 = (select sum(DET_VAL5) from MYDETAIL where DET_HDR =
> h.HDR_KEY),
> HDR_VAL6 = (select sum(DET_VAL6) from MYDETAIL where DET_HDR =
> h.HDR_KEY),
> HDR_VAL7 = (select sum(DET_VAL7) from MYDETAIL where DET_HDR =
> h.HDR_KEY)
> from MYHEADER h
> But I guess this code CAN cause delay. And talking about performance
> procedures can keep a execution plan,a nd execute it fast.
> Marcelo
> "Stephen Eyton-Jones" <sjo...@hmssoftware.ca> escreveu na mensagem
> news:3d19e821$1_2@dnews...
> > Wow, I appreciate the work you did here, but I was looking for a single
> SQL
> > statement to do this. I'm guessing that by your response I cannot do it
> > without writing a stored procedure which you have graciously done for
me.
> > To answer your question we support Sybase, SQL, Oracle, Interbase and
> > Informix, so having the ones you gave me is very good.
> > Thanks
> > Steve
> > "Marcelo Hideki Hirota" <hid...@mps.com.br> wrote in message
> > news:3d18b95a$1_2@dnews...
> > > Sorry,
> > > For Oracle I forgot to put the ';' in the end of commands, like:
> > > > where HDR_KEY = AuxCursor.DET_HDR;
> > > > END LOOP AuxCursor;
> > > For SQL Server you have to replace system variables: "@@sqlstatus<>2"
> for
> > > "@@fetch_status = 0", "@@sqlstatus=1" for "@@fetch_status != 0"
(note:
> > the
> > > code related is useless in SQL Server ) end "@@transtate in (2,3)" for
> > > "@@error <> 0"
> > > Once and again I couldn't test it, ok.
> > > Marcelo
> > > "Marcelo Hideki Hirota" <hid...@mps.com.br> escreveu na mensagem
> > > news:3d18b3f5_1@dnews...
> > > > You didn't tell us wich database server you're using. I didn't test
> > them,
> > > > it's just a shot.
> > > > -- For Oracle you can try this one:
> > > > FOR AuxCursor IN ( select DET_HDR,
> > > > sum(DET_VAL1) as DET_VAL1,
> > > > sum(DET_VAL2) as DET_VAL2,
> > > > sum(DET_VAL3) as DET_VAL3,
> > > > sum(DET_VAL4) as DET_VAL4,
> > > > sum(DET_VAL5) as DET_VAL5,
> > > > sum(DET_VAL6) as DET_VAL6,
> > > > sum(DET_VAL7) as DET_VAL7
> > > > from MYDETAIL group by DET_KEY order
> by
> > > > order by DET_KEY ) LOOP
> > > > UPDATE MYHEADER
> > > > set HDR_VAL1 = AuxCursor.DET_VAL1,
> > > > HDR_VAL2 = AuxCursor.DET_VAL2,
> > > > HDR_VAL3 = AuxCursor.DET_VAL3,
> > > > HDR_VAL4 = AuxCursor.DET_VAL4,
> > > > HDR_VAL5 = AuxCursor.DET_VAL5,
> > > > HDR_VAL6 = AuxCursor.DET_VAL6,
> > > > HDR_VAL7 = AuxCursor.DET_VAL7
> > > > where HDR_KEY = AuxCursor.DET_HDR
> > > > END LOOP AuxCursor
> > > > -- For Sybase/SQL Server you can try this one
> > > > declare _AuxCursor cursor for
> > > > select DET_HDR,
> > > > sum(DET_VAL1) as DET_VAL1,
> > > > sum(DET_VAL2) as DET_VAL2,
> > > > sum(DET_VAL3) as DET_VAL3,
> > > > sum(DET_VAL4) as DET_VAL4,
> > > > sum(DET_VAL5) as DET_VAL5,
> > > > sum(DET_VAL6) as DET_VAL6,
> > > > sum(DET_VAL7) as DET_VAL7
> > > > from MYDETAIL group by DET_KEY order
> by
> > > > DET_KEY
> > > > open _AuxCursor
> > > > fetch _AuxCursor
> > > > into @DET_HDR, @DET_VAL1, @DET_VAL2, @DET_VAL3, @DET_VAL4,
@DET_VAL5,
> > > > @DET_VAL6, @DET_VAL7
> > > > while @@sqlstatus<>2
> > > > begin
> > > > if @@sqlstatus=1
> > > > begin
> > > > rollback transaction
> > > > raiserror 55000 'Error reading _AucCursor (MYDETAIL)'
> > > > break
> > > > end
> > > > updater MYHEADER
> > > > set HDR_VAL1 = @DET_VAL1,
> > > > HDR_VAL2 = @DET_VAL2,
> > > > HDR_VAL3 = @DET_VAL3,
> > > > HDR_VAL4 = @DET_VAL4,
> > > > HDR_VAL5 = @DET_VAL5,
> > > > HDR_VAL6 = @DET_VAL6,
> > > > HDR_VAL7 = @DET_VAL7
> > > > where HDR_KEY = @DET_HDR
> > > > if @@transtate in (2,3)
> > > > begin
> > > > raiserror 55000 'Error updating MYHEADER'
> > > > break
> > > > end
> > > > fetch _AuxCursor
> > > > into @DET_HDR, @DET_VAL1, @DET_VAL2, @DET_VAL3, @DET_VAL4,
@DET_VAL5,
> > > > @DET_VAL6, @DET_VAL7
> > > > end
> > > > close _AuxCursor
> > > > deallocate cursor _AuxCursor
> > > > "Stephen Eyton-Jones" <sjo...@hmssoftware.ca> escreveu na mensagem
> > > > news:3d18a002$1_1@dnews...
> > > > > I'm trying to create an update statement that will summarize some
> > values
> > > > for
> > > > > me in one statement.
> > > > > Basically I have one header table MYHEADER and one detail table
> > > (MYDETAIL)
> > > > > with a one to many relationship from header to detail.
> > > > > MYHEADER has...
> > > > > HDR_KEY NUMERIC 11 [Unique key]
> > > > > HDR_VAL1 NUMERIC 11 [Summary Header Value]
> > > > > HDR_VAL2 NUMERIC 11 [Summary Header Value]
> > > > > HDR_VAL3 NUMERIC 11 [Summary Header Value]
> > > > > HDR_VAL4 NUMERIC 11 [Summary Header Value]
> > > > > HDR_VAL5 NUMERIC 11 [Summary Header Value]
> > > > > HDR_VAL6 NUMERIC 11 [Summary Header Value]
> > > > > HDR_VAL7 NUMERIC 11 [Summary Header Value]
> > > > > example of header data
> > > > > 1,0,0,0,0,0,0,0
> > > > > 2,0,0,0,0,0,0,0
> > > > > MYDETAIL has
> > > > > DET_KEY NUMERIC 11 [Unique Key]
> > > > > DET_HDR NUMERIC 11 [Key to related header]
> > > > > DET_VAL1 NUMERIC 11 [Detailed Value]
> > > > > DET_VAL2 NUMERIC 11 [Detailed Value]
> > > > > DET_VAL3 NUMERIC 11 [Detailed Value]
> > > > > DET_VAL4 NUMERIC 11 [Detailed Value]
> > > > > DET_VAL5 NUMERIC 11 [Detailed Value]
> > > > > DET_VAL6 NUMERIC 11 [Detailed Value]
> > > > > DET_VAL7 NUMERIC 11 [Detailed Value]
> > > > > example of detail data
> > > > > 100,1,0,60,120,40,180,480,0
> > > > > 101,1,0,120,0,60,100,200,0
> > > > > 102,1,0,120,0,60,100,200,0
> > > > > 103,1,0,120,0,60,100,200,0
> > > > > 104,2,0,60,120,40,180,480,0
> > > > > 105,2,0,120,0,60,100,200,0
> > > > > 106,2,0,120,0,60,100,200,0
> > > > > 107,2,0,120,0,60,100,200,0
> > > > > What I want now to do is to summarize the values from the detail
> table
> > > > into
> > > > > the header in one statement for all rows.
> > > > > In English multiple line syntax
> > > > > SELECT
