hello everyone in baanboard ....i'm newbie in Baan and just start learning everything about it...i came across a question in combining the data in a table tat i had attach in excel 2003 format. please guide me through where and wat i need to do to solve my problem....million thanks for help and guide given.

sorry actually is not on table ....is on the report......

i had just add a code segment of

before.program:
db.retry.point()
select tdkpm620.*
from tdkpm620
where tdkpm620.prod = tdkpm620.prod
and tdkpm620.mach = tdkpm620.mach
and tdkpm620.trdt = tdkpm620.trdt
and tdkpm620.subs = tdkpm620.subs
and tdkpm620.ttyp = tdkpm620.ttyp
selectdo tdkpm620.loca ="M+S"
db.update(ttdkpm620,db.retry)
selecteos
commit.transaction()
endselect

in the report script....i dont know wat this code had done ...but data in the table tdkpm620 had been duplicated!!!!
in this threat i also attach the report in excell format, the program script and the report script......million thanks for helps given~~~5093

5094

5095

Are you talking the reports have duplicate records? I don't see anything that would duplicate records in the table itself.

To solve this problem I usually use nested selects - first thing that popped into my mind:

select product, machine, subs, type, size&length
from table
where ....
group by product, machine, subs, type, size&length
selectdo
|The above select groups the records
rprt.fgsqty = 0
rprt.fgoods = 0
select sum(a.fgsqty):rprt.fgsqty, sum(a.finishedgoods):rprt.fgoods
from table a
where a.product = :table.product
and a.machine = :table.machine
and a.subs = :table.subs
and a.type = :table.type
and a.size&length = :table.size&length
selectdo
endselect
| Above queries give total of qty and weight columns. You
| could also do one to add the locations to each other in
| another query
rprt_send()
endselect

This part of code would match any record of the table, but it will result in a runtime error because you make a db.update of the record without locking it


[before.program:
db.retry.point()
select tdkpm620.*
from tdkpm620
| to lock the selected record you have use the following syntax
| from tdkpm620 for update
| the following matches all records of the table, because you compare the
| fields with itself
where tdkpm620.prod = tdkpm620.prod
and tdkpm620.mach = tdkpm620.mach
and tdkpm620.trdt = tdkpm620.trdt
and tdkpm620.subs = tdkpm620.subs
and tdkpm620.ttyp = tdkpm620.ttyp
selectdo tdkpm620.loca ="M+S"
db.update(ttdkpm620,db.retry)
selecteos
commit.transaction()
endselect
Can you explain, what you wanted to do with this code

Holger

This part of code would match any record of the table, but it will result in a runtime error because you make a db.update of the record without locking it


[before.program:
db.retry.point()
select tdkpm620.*
from tdkpm620
| to lock the selected record you have use the following syntax
| from tdkpm620 for update
| the following matches all records of the table, because you compare the
| fields with itself
where tdkpm620.prod = tdkpm620.prod
and tdkpm620.mach = tdkpm620.mach
and tdkpm620.trdt = tdkpm620.trdt
and tdkpm620.subs = tdkpm620.subs
and tdkpm620.ttyp = tdkpm620.ttyp
selectdo tdkpm620.loca ="M+S"
db.update(ttdkpm620,db.retry)
selecteos
commit.transaction()
endselect
Can you explain, what you wanted to do with this code

Holger
Good catch - I did not see he was missing the :'s. This is part of his other post on trying to group records in a report. I will combine the threads.

sorry for late reply guys....and thanks a lot for the information given...i will had to wait till the database is corrected by my senior then i will try the solution given.......
for the code ...
before.program:
db.retry.point()
select tdkpm620.*
from tdkpm620
where tdkpm620.prod = tdkpm620.prod
and tdkpm620.mach = tdkpm620.mach
and tdkpm620.trdt = tdkpm620.trdt
and tdkpm620.subs = tdkpm620.subs
and tdkpm620.ttyp = tdkpm620.ttyp
selectdo tdkpm620.loca ="M+S"
db.update(ttdkpm620,db.retry)
selecteos
commit.transaction()
endselect

i was hoping that it will merge the data that having similarity then i can group it into field<location> named "M+S"....but my sql knowledge sure is limited...and i randomly takes some code from the forum and tested it...leading to random duplicated data in table tdkpm620......

Are you talking the reports have duplicate records? I don't see anything that would duplicate records in the table itself.

To solve this problem I usually use nested selects - first thing that popped into my mind:

select product, machine, subs, type, size&length
from table
where ....
group by product, machine, subs, type, size&length
selectdo
|The above select groups the records
rprt.fgsqty = 0
rprt.fgoods = 0
select sum(a.fgsqty):rprt.fgsqty, sum(a.finishedgoods):rprt.fgoods
from table a
where a.product = :table.product
and a.machine = :table.machine
and a.subs = :table.subs
and a.type = :table.type
and a.size&length = :table.size&length
selectdo
endselect
| Above queries give total of qty and weight columns. You
| could also do one to add the locations to each other in
| another query
rprt_send()
endselect


i'm just had to combine identical data on the field u had mention on the code above and group it into "Machine+Store" in field<location> that i had shown in the 1st excell file ......and for duplicated data ....it's happen after i added the code tat i had shown in the second case.....