RE: Another SQL headbanger -- RPG400-L

Pete,

You definitely want to download this REDBOOK:

Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database for iSeries
http://www.redbooks.ibm.com/abstracts/sg246503.html?Open

SQL Stored Procedures ~= RPG IV Programs
SQL User Defined Functions ~= RPG IV Procedures

UDFs can be external, with the code written in RPG, CL, COBOL, C, Java, ect, or SQL UDFs written in
SQL as in my example. Stored procedures work the same way.

There are two types of UDFs:
1) Scalar, which return a single value like in my example
2) Table, which return a "temporary table" or results set that can be used in the FROM clause of a SQL
SELECT statement.

As with stored procedures, when you create a SQL UDF the system actually creates a ILE C program.
While both SPs and UDFs live in a particular library you can call them system wide. You can
save/restore both SPs and UDFs.

One important thing to keep in mind with UDFs is that the DB expects them to return quickly. There is
in fact a timeout after which the DB will throw an exception message. Default is 30sec and is
controlled by QAQQINI. Given that a UDF could conceivably be invoked for every row of a million row
table, you can see why they should be fast.

HTH,
Charles

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Pete Helgren
Sent: Friday, September 07, 2007 9:18 AM
To: RPG programming on the AS400 / iSeries
Subject: 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.

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