We have a session that gathers data from another table and inserts it into a new session.
We have a problem that we need to run this daily, hovever, the same data is being imported several times into this table.

They have all the same information but the sequence number is different.

For example

sequence sched no issuedt reqiredt quantity

163 123456 24-01-2005 24-01-2005 100
164 123456 24-01-2005 24-01-2005 100

How do I write a script to remove the 163 (lower sequence numbers).
This may be repeated more than once (there could be a 165 seq with the same duplicate information.

Hi,

One easy way to do it is, for every record you are trying to enter should be unique, so before inserting any record check whether that record exists and then if it is NOt then insert.

You need NOT remove duplicate records after inserting it.

if this is NOT what you are looking for, try to post more info.

Regards

minor change in the logic of user "baanprog" would be to overwrite previous
record with the current record if you intend to retain the latest.

compare the fields (like sched no and qty, assuming they define the uniqueness) and process records.

Do you really care what sequence number remains?

In pseudo-SQL it would go like this:

Step 1 would be to identify the dupes.

select 1 from [YourTable]
where count (seq.number) > 1
order by [all the other fields]

step 2 is to wrap the above in the actual transaction, so..

remove record
from [YourTable]
where exists
[the previous query]

That should do the trick

I need to keep the highest sequence number.
Wha i need is to sort if the issue date and the requirement date is duplicated then select the highest sequence number to keep and delete the duplicates that are found.

As I read your problem you have duplicate records that you would like to remove and then you would like to get your new script to not do it again. If that is correct then I'd create a correction program with a simple script that would hold the sequence number and as the query read the records in order/date/date/seq order. If a hold.seq is found then delete the record.

select *
from table
where date range?
order by order/date/date/seq
selectdo
if hold.orderno=table.orderno
and hold.date1=table.date1
and hold.date2=table.date2
and hold.seq=table.seq then
db.delete(..)
endif
hold.orderno=table.orderno
hold.date1=table.date1
hold.date2=table.date2
hold.seq=table.seq
endselect

Once you have corrected the data then use the logic that others have suggested to insert only unique records.

Thanks for your help, this seems to work.

Try this ... I added the nested query for delete of lower seq numbers.

select tdazt050.*
from tdazt050
where tdazt050._index4 inrange {:cuno.f, :cdel.f, :item.f, :cont.f, :pono.f, :sanl.f, :seqn.f}
and {:cuno.t, :cdel.t, :item.t, :cont.t, :pono.t, :sanl.t, :seqn.t}
order by tdazt050.schn, tdazt050.sanl, tdazt050.seqn, tdazt050.iqty
selectdo

if hold.schn = tdazt050.schn
and hold.sanl = tdazt050.sanl
and hold.iqty = tdazt050.iqty
and hold.seqn = tdazt050.seqn
then
delete.lower.seqn()
endif

hold.schn = tdazt050.schn
hold.sanl = tdazt050.sanl
hold.iqty = tdazt050.iqty
hold.seqn = tdazt050.seqn

endselect

commit.transaction()


function delete.lower.seqn()
{
select tdazt050.*
from tdazt050 for update
where tdazt050.schn = hold.schn
and tdazt050.sanl = hold.sanl
and tdazt050.iqty = hold.iqty
and tdazt050.seqn = hold.seqn
as set with 1 rows
selectdo
db.delete(ttdazt050, db.retry)
endselect

commit.transaction()
}

Still not exactally working properly.

The original requirement changed a bit.

I need to delete all records that have duplicated requirement dates and keep the requirement that has the higher sequence number.

This is my Situation

Customer :101705
Del :001
Item :12345678
Contract :10000
Position :100

Seq Sched Issue Date Quantity
10 12345 07-11-2004 150
12 12345 07-12-2004 180
15 12345 07-12-2004 180
16 12356 07-12-2004 150
17 12345 07-14-2004 120

In this example i need to remove se 12&15 (issue date is found in seq 16)
How can I do this (look for issue date = issue date)/sort by sequence. Delete lower sequence numbers that have matching issue date.

Itannous, in big lines, do the following:



domain tccom.bpid hold.cust
domain tcmcs.long hold.sced
domain tcdate hold.sdat

hold.cust = ""
hold.sced = 0
hold.sdat = 0

db.retry.point()

select table.*
from table for update
where table.cust inrange :cust.f and :cust.t |selection range can be defined via form-fields
and table.sdat inrange :date.f and :date.t |if you can select on indices, this is best of course
order by table.sdat desc, table.seqn desc
selectdo
if table.cust = hold.cust and
table.sced = hold.sced and
table.sdat = hold.sdat then |(if you have more crucial conditions here, you can add them)
db.delete(ttable, db.retry)
count = count + 1
if count > 250 then
commit.transaction()
count = 0
endif
else
hold.cust = table.cust
hold.sced = table.sced
hold.sdat = table.sdat
endif
selectempty
endselect

commit.transaction()


Hope this is clear. Of course you have to adjust this with correct table names, field names, etc according to your situation...

Good luck!!

En

It seemed to work, but when I logged backin to the system, it didn't work
I created runtime on the sesison , but still not working
What am I missing. I have included the entire session script

declaration:

table ttdazt050 | Customer History Evaluation Table


extern domain tcdate sanl.f
extern domain tcdate sanl.t
extern domain tccuno cuno.f fixed
extern domain tccuno cuno.t fixed
extern domain tccdel cdel.f fixed
extern domain tccdel cdel.t fixed
extern domain tcitem item.f fixed
extern domain tcitem item.t fixed
extern domain tccono cont.f
extern domain tccono cont.t
extern domain tdpsc.pono pono.f
extern domain tdpsc.pono pono.t
extern domain tdssc.seqn seqn.f
extern domain tdssc.seqn seqn.t
domain tccdel hold.cdel
domain tccuno hold.cuno
domain tcitem hold.item
domain tccono hold.cont
domain tcdate hold.sanl
domain tdssc.seqn hold.seqn
domain tdssc.schn hold.schn
domain tcqiv1 hold.iqty
domain tcpono hold.pono
|****************************** form section **********************************

form.1:
init.form:
get.screen.defaults()

|****************************** choice section ********************************

choice.cont.process:
on.choice:
read.main.table()


|****************************** field section *********************************

field.sanl.f:
when.field.changes:
sanl.t = sanl.f

field.cuno.f:
when.field.changes:
cuno.t = cuno.f

field.cdel.f:
when.field.changes:
cdel.t = cdel.f

field.item.f:
when.field.changes:
item.t = item.f

field.cont.f:
when.field.changes:
cont.t = cont.f

field.pono.f:
when.field.changes:
pono.t = pono.f

field.seqn.f:
when.field.changes:
seqn.t = seqn.f


|****************************** function section ******************************

functions:

function read.main.table()
{


long count
hold.cuno = ""
hold.cdel = ""
hold.schn = 0
hold.sanl = 0
hold.item = ""
hold.cont = 0
hold.seqn = 0
db.retry.point()

select tdazt050.*
from tdazt050 for update
where tdazt050._index3 inrange {:sanl.f, :cuno.f, :cdel.f,
:item.f, :cont.f, :pono.f, :seqn.f}
and {:sanl.t, :cuno.t, :cdel.t, :item.t, :cont.t,
:pono.t, :seqn.t}
order by tdazt050.sanl desc, tdazt050.seqn desc
selectdo
if tdazt050.sanl = hold.sanl
and tdazt050.item = hold.item
and tdazt050.cuno = hold.cuno
and tdazt050.cont = hold.cont
and tdazt050.pono = hold.pono
then
db.delete(ttdazt050, db.retry)
count = count +1
if count > 250 then
commit.transaction()
count = 0
endif
else


hold.sanl = tdazt050.sanl
hold.item = tdazt050.item
hold.cont = tdazt050.cont
hold.cuno = tdazt050.cuno
hold.pono = tdazt050.pono
endif
selectempty
endselect
commit.transaction()

}

Heya!

What do you mean with "it seemed to work, but when I logged back into the system it didn't"...?? What worked? First of all did you compile the script, the form and the session? Were there no errors? Did you already run the session succesfully once (is that what you mean with first it seemed to work...?)? And what is not working now?

When I ran it after making the script changes, it worked. It deleted the duplicated records.
After running another process that creates the duplicate entries, i ran this session again (after I logged out of the system and back in), and now it does not remove the duplicate values...
I created runtime on the session and form but no luck

I am not getting errors, but a warning which I dont under stand

ptdazt9999g (217):Warning(5): Statment not reached..

Whats 217 (i dont have a line 217)

When I enter a specific part number and customer in my main form it works
But when i leave the range completely open, no data is deleted.

How is this so??

What do you mean with completely open? from "" to ZZZZZZZZZZZZZZZZZZZ and from 0 to 999999999, or both from and to fields empty/zero? If the latter, of course nothing will be selected.

I changed the if statment to only include if tdazt050.sanl = hold.sanl
and tdazt050.item = hold.item

Not sure why the hold.cuno and others caused an issue

Thanks for you help

I don't know what those other fields were, but are you sure they were always equal for the duplicate records??? If yes, I indeed don't understand what happened there, but then you also have to make sure something else: is it not too risky to limit the if-statements? In other words, are you certain that you always remove the right records; is the check on only sanl and item enough to determine that you are dealing with the required records? If not, you are going to remove the worng records and mess up your data....