Re: Another SQL headbanger

Wow! Thanks. I need to spend some time getting to know SQL.

I haven't worked with UDF's before so I need just a bit of background. When I create the UDF, is it then available to use system wide? I have created stored procedures that then live in the library I created them in but is a UDF something that resides at the system level? This would also be something that I would have to create uniquely on every system that I wanted to run the UDF on vs being able to save an "object" that represents the UDF and restore it to another system, correct?

I'll give this a whirl. I really appreciate the information.

Pete


Wilt, Charles wrote:

You don't need the group by if you use a correlated subquery.

update thetable A
set Date = :newdate,
line = 1 + ( select coalesce( max( line ) , 0 )
from thetable B
where b.Date = :newdate and a.empID = b.empID )
where date = :olddate

However, the above doesn't work. You end up with:
EMPID MYDATE LINE
333 70,715 1
333 70,715 2
333 70,715 3
333 70,715 3
446 70,715 1
446 70,715 2
446 70,715 3
446 70,715 3
500 70,715 1
500 70,715 2
500 70,810 1


If you run the above in debug you'll see the following message:
Temporary file built for file THETABLE.
A temporary file was built for member THETABLE of file THETABLE in library QTEMP for reason code 4.
4 - For an update-capable query, a subselect references a field in this file which matches one of the fields being updated.

To get this to work, you'll need to use a UDF that returns the currect max value of the LINE field.

create function GetMaxLine ( emp char(3), dte dec(6,0)) returns integer language SQL NOT DETERMINISTIC return coalesce((select max(line) from charles where empid = emp and mydate = dte ),0)
update charles A set myDate = 070715, line = 1 + GetMaxLine(a.empID,070715) where mydate = 070725
EMPID MYDATE LINE
446 70,715 1
446 70,715 2
446 70,715 3
446 70,715 4
500 70,715 2
500 70,715 1
500 70,810 1
333 70,715 1
333 70,715 2
333 70,715 3
333 70,715 4

The important part of the UDF is the NOT DETERMINISTIC. The tells the DB to rerun the function for
every call and not to reuse the prior results if the parameters are the same.

Note, with a large table, you need to make sure you have an index on empID, date, line.

HTH,
Charles

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Pete Helgren
Sent: Thursday, September 06, 2007 6:05 PM
To: RPG programming on the AS400 / iSeries
Subject: 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.

--
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.

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.