Hi,

I am using a new customized table. I would like to copy all records from tdsls045 on selection of date range to this new table. Following is the code , but it returns with Error 100.There are no duplicate records as per primary key. Please guide.

86 select tdsls999.*
87 from tdsls999 for update
88 where tdsls999._index1 inrange {:ddat.f, :orno.f, :pono.f,
89 :srnb.f, :fono.f, :ckor.f}
90 and {:ddat.t, :orno.t, :pono.t, :srnb.t, :fono.t,
91 :ckor.t}
92 order by tdsls999._index1
93 as set with 1 rows
94 selectdo
95
96 tdsls999.runo = run.number
97 tdsls999.seno = 0
98 dat = dte$()
99 work.date = dat(3;2) & dat(1;2) & dat(5;2)
100 todays.date = inputstr.to.date(work.date,"%D002,3")
101 tdsls999.rdat = todays.date
102 tdsls999.rtim = dat(7;2) & ":" & dat(11;2)
103 tdsls999.ddat = tdsls045.ddat
104 tdsls999.cuno = tdsls045.cuno
105 tdsls999.orno = tdsls045.orno
106 tdsls999.ccod = "00000"
107 tdsls999.ccty = "FAR"
108 tdsls999.unit = "PPP"
109 tdsls999.pall = 2
110 tdsls999.nama = "ABC and and "
111 tdsls999.dnam = "BDF and and "
112 tdsls999.pstc = "XZZ"
113 tdsls999.pono = tdsls045.pono
114 tdsls999.srnb = tdsls045.srnb
115 | tdsls999.fono = tdsls043.fono
116 | tdsls999.ckor = tdsls043.ckor
117 | update set
118 db.insert(ttdsls999, db.retry)
119 | count = count + 1
120 | if count > 50 then
121 | count = 0
122 if not db.error(ttdsls999) then
123
124 commit.transaction()
125 else
126 emergency.exit(form.text$("tigl.hras0003"))
127
128 endif
129endselect
130}

Many Thanks , abr

Hi there abr, yes there are no duplicate values to be found on your table because baan is refusing to add the duplicate record. You don't show the code before entering this select statement however I assume the code selects on tdsls045. What happens when the program is executed twice (another time) of course the 'selectdo' code is executed on sls999 because the where clause matches. You have two choices:
1. move the db.insert to selectempty (which makes more sense)
2. add the option db.skip.dupl to your db.insert (always a good idea!)

Regards,
Rupert

Hi Rupert,

Thanks for your reply. There is not more code above what i have pasted other then declaration. I did not include tdsls045 in selection can you please tell me where to include same. I have got similar code as select do for selectempty but i did not include same in post as it would have become long. I have included db.skip.dupl but still it has not inserted anymore records then the first record which has got 3 fields Run number(runo). Date and time and rest of the fields are not copied. Run number is copied from another function.

Many Thanks,

abr

Abr,

Does the table tdsls999 have a secondary key? and does that secondary key allow duplicates?


Just something to check

Steve

Hi Steve,

There are sec. keys and allow duplicate is ticked yes.

Many Thanks,

abr

Abr, if you don't select anything from tdsls045 then 'ddat', 'orno', 'pono', 'srnb' will default to 0 every time - meaning that as those are your primary key fields every record looks the same hence the error 100. You must use sls045 as your outer loop then verify that the record does not exist on sls999 (selectempty) and do the db.insert in the selectempty.

This will surely solve your problem...
Rupert

Hi Rupert,

I have included tdsls045 in selection and it works fine.

Thanks,

abr