sql to create value dependent on prior record
sql to create value dependent on prior record
(OP)
Is Access/Jet Table is this:
id, wo,
1, 2345-1
2, 2345-1
3, 2345-1
4, 5432-1
5, 5432-1
6, 5432-1
Need result:
id, wo, parent
1, 2345-1, 1
2, 2345-1, 1
3, 2345-1, 2
4, 5432-1, 4
5, 5432-1, 4
6, 5432-1, 5
the logic is If wo = wo of ID-1 then Parent = Id-1
If wo not= wo of ID-1 then Parent = Id
could use help on the correct sql statement for this...
thanks in advance...
id, wo,
1, 2345-1
2, 2345-1
3, 2345-1
4, 5432-1
5, 5432-1
6, 5432-1
Need result:
id, wo, parent
1, 2345-1, 1
2, 2345-1, 1
3, 2345-1, 2
4, 5432-1, 4
5, 5432-1, 4
6, 5432-1, 5
the logic is If wo = wo of ID-1 then Parent = Id-1
If wo not= wo of ID-1 then Parent = Id
could use help on the correct sql statement for this...
thanks in advance...





RE: sql to create value dependent on prior record
execute block returns(id_out integer,wo varchar(6),parent integer)
as
declare variable n integer;
declare variable i integer;
declare variable wo_old varchar(6);
begin
select count(*) from table1 into :n;
select id,name,id as parent from table1 order by id rows 1 to 1 into :id_out,:wo,:parent;
suspend;
i=2;
while(i<=n) do begin
select name from table1 order by id rows (:i-1) to (:i-1) into :wo_old;
select id, name from table1 order by id rows :i to :i into :id_out, :wo;
if (wo_old=wo) then
parent=id_out-1;
else
parent=id_out;
i=i+1;
suspend;
end
end
It's a dynamic procedure, where name is your wo column. Tested & Works! Hope that help!
rtmpxr