I want to update a status field against each record where I have the same reference number.

e.g. I have five records each one has a ref number of AWB1. The first record in this subset has data populated in a status field. I am trying to create a session that will select all records which have the same ref number and makes sure that the status field in each record is the same.

Records:-

1. Ref = AWB1 Status = OK
2. Ref = AWB1 Status = <blank>
3. Ref = AWB1 Status = <blank>
4. Ref = AWB1 Status = <blank>
5. Ref = AWB1 Status = <blank>

So I want my script to select all records in the table and where the ref is the same make sure all status fields are the same.

Below is my verson 1 script. The session runs but nothing gets updated.


function l.status()
{
select tdsls946.*
from tdsls946
selectdo
status = tdsls946.status
awb = tdsls946.awb
select tdsls946.status
from tdsls946 for update
where tdsls946.awb = :awb
selectdo
tdsls946.status = status
endselect
endselect
}


I'm sure there is a simple solution to this, simple to you guys anyway!

Any help you can offer will be appreciated.

Regards

PJ

Two things jump out:

There is no db.update(ttdsls946, db.retry) to actually do the update
Your outer select loop is suspicious. I'm guessing a group by clause is probably appropriate. However, how are you deciding which status to use if rows have different status values?


Depending on where in your program this exists, you may also need db.retry.point() and commit.transaction().

I think that the question lacks a bit of information...
which is the "right" status?
I suppose, in your case, "OK" and not <blank> - but suppose I have the records with those values:

"OK"
""
"NOT OK"
"PERHAPS OK"
"ALMOST OK"
"MAYBE NOT OK"

Which value you have to assign?
if the value to assign is the maximum status, you can use something like this:

domain ..... maxstatus
db.retry.point()
select tdsls946.awb, max(tdsls946.status):maxstatus
from tdsls946
group by tdsls946.awb
selectdo
select tdsls946.*
from tdsls946 for update
where tdsls946.awb = :tdsls946.awb
selectdo
tdsls946.status = maxstatus
db.update(ttdsls946, db.retry)
endselect
endselect
commit.transaction()

You've both mentioned something that I too realised after I posted the thread. Which status to use!!

The table is holding delivery tracking iformation from our couriers. So the status of records with the same AWB number could go through many status updates. Our system is used to ref Baan pack slips against courier airway bills. The problem is that many pack slips can be shipped against one airway bill number. Something that the system was not designed for!!

I'll need to put some more thought to this.

Regards

PJ