Insert Into Select - I need each row Selected to be dependent on orecords previously inserted

[new]

I am building an effective dated file that shows results to date.

In part 1 of the select I
I process a larges set of input and genrate records for each date in the
processing period for which there has been activity

In part II I then want to select the most recent effective dated record (if
any and whether it existed before this run or was generated by an earlier
insert) and sum it with the record for this date

So my question is: when do the insertions happen and when does the selection
happen so that I can "see" the results of an earlier insertion?

I beleive that this is related to the problem I have seen dealt with before:

insert into tableA select * from tableB where not exists in tableA

I know this could be solved by doing a Select distinct on table B (see below
not that it matters) but I believe I saw a solution where there was a commit
immeadiate or some such thing that caused the exist clause to be evaluated
against tableA including records added earlier.



insert into tableA
select Distinct from tableB
where not exists in tableA

or

insert into tableA
select * from ( select Distinct from tableB ) as tableBB
where not exists in tableA

[Serge Rielau]

new wrote:

I am building an effective dated file that shows results to date.

In part 1 of the select I
I process a larges set of input and genrate records for each date in the
processing period for which there has been activity

In part II I then want to select the most recent effective dated record (if
any and whether it existed before this run or was generated by an earlier
insert) and sum it with the record for this date

So my question is: when do the insertions happen and when does the selection
happen so that I can "see" the results of an earlier insertion?

I beleive that this is related to the problem I have seen dealt with before:

insert into tableA select * from tableB where not exists in tableA

I know this could be solved by doing a Select distinct on table B (see below
not that it matters) but I believe I saw a solution where there was a commit
immeadiate or some such thing that caused the exist clause to be evaluated
against tableA including records added earlier.



insert into tableA
select Distinct from tableB
where not exists in tableA

or

insert into tableA
select * from ( select Distinct from tableB ) as tableBB
where not exists in tableA

By definition th entire select is processed before teh first row is
inserted.
Otherwise side-effect such as the "Halloween effect" can occur
(http://www.mcjones.org/System_R/SQL_...95-System.html
search for "Halloween")

If you want to see previously inserted rows for each insert you will
need to do single row insert.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/