Re: Another SQL headbanger

Thanks Brian.

This looks close to what I am looking for, but I am not sure, so I am going to add a bit more data to the example and propose an sql statement.

Records look like:

EmpID Date (YYMMDD) Line#
446 070715 1
446 070715 2
446 070725 1
446 070725 2
500 070725 1
500 070715 1
500 070810 1
333 070715 1
333 070715 2
333 070725 1
333 070725 2

So with the above data, if I were to re date the 070725 records to 070715 wed have 4 potential duplicates. Normally that update would be:

update myfile set date = 070715 where date = 070725. OR in the case of using host variables: update myfile set date = :newDate where date = :oldDate.

Lets assume that no other host variables are passed. What would the SQL look like? I think coalesce is the way to go, but I am concerned about what the line numbers would be set to. Depending upon the number of records in the set and then highest line number, I think I would need to use a "group by" clause to get the line numbers associated with each record (I think).

Pete


Brian Johnson wrote:

update thetable
set Date = :newdate,
line = 1 + ( select coalesce( max( line ) , 0 )
from thetable
where EmpID = :empid and Date = :newdate )
where empid = :empid
and date = :olddate
and line = :line


On 9/6/07, Pete Helgren <Pete@xxxxxxxxxx> wrote:

I have another issue where I am updating a file using SQL and it could
potentially create duplicates. Rather than omit the duplicates, I want
to modify the values so the duplicates won't occur. There is a line
number field in the table that I can increment by 1 if I find a record
that is a duplicate and that will eliminate a duplicate value.

For example the data might be

EmpID Date Line#
446 071507 1
446 071507 2
446 072507 1

So if I want to change the date from 072507 to 071507 I also need to
change the line to 3.

Any ideas ?

Pete Helgren

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.