Re: Anyone using the *AFTER *READ Triggers in V5R1/V5R2?

Booth:

As an initial disclaimer, I'll point out that the original question specified 
V5R1/V5R2. UDFs (User-Defined Functions) may be much easier for many to create 
on version 5 due to how IBM has changed what gets installed with the SQL 
development kit and related items. Also, I am _not_ a UDF development guru nor 
teacher, so anything I write will be over-simplified and possibly wrong in 
detail at points; but others can correct and amplify as needed.

On an AS/400, think of "tables" as physical files and "views" as logical files. 
Therefore, an SQL 'CREATE TABLE' statement is a way of doing a CRTPF and 
getting an externally-described PF without using DDS. Similarly, an SQL 'CREATE 
VIEW' statement does a CRTLF without needing DDS. The DDS is more or less 
replaced by using different clauses in the SQL statements.

If you create a "normal" LF with DDS, it's possible to create fields that don't 
really exist in the physical database. You might CONCAT two physical fields to 
create a virtual field that doesn't really exist. Or you might SST a field to 
extract only a portion. Maybe you'd join an order detail file to an item master 
file in order to pull an item description in when the order details are read -- 
though the item description isn't actually kept in the order detail records, 
the database has been told how to supply the item description anyway.

Similar results can be had through SQL views and definitely functions, but the 
results are far less limited. Essentially, by creating a function and 
referencing the function in a view, you've created a more or less hidden 
database program that supplies the value for one of the fields in a logical 
file, just as CONCAT supplies a value. If you say that "CONCAT" is a builtin 
function for DDS, then a UDF is a function you create yourself. An RPG program 
that reads that view doesn't know that it's not really reading a physical file 
and it doesn't care.

Beyond that, it turns into teaching SQL and UDFs; so that's enough. Go to the 
the Info Center and just search on "udf sql" and you'll find lots of stuff. 
Type some CREATE VIEW or CREATE FUNCTION statements into a source member and 
execute them with RUNSQLSTM, or use OpsNav. If you have interactive SQL, that's 
a better place to play with CREATE VIEW because of the prompting help. (Hint: 
Use DROP VIEW to clean up after.)

Lots of fun stuff even if you can't find a use. Ever tried to create an LF over 
another LF? No problem when you use a CREATE VIEW instead.

Just don't try to return a text value. (Joe, I'm kidding!)

Tom Liotta

midrange-l-request@midrange.com wrote:

>  13. Re: Anyone using the *AFTER *READ Triggers in V5R1/V5R2? (Booth Martin)
>
> Tom, can you explain this a bit?  I am a RPG guy and not too sure what a
>view" is, and have no idea what a UDF is.  It seems important though.
>thanks.

--
--
Tom Liotta
The PowerTech Group, Inc.
19426 68th Avenue South
Kent, WA 98032
Phone  253-872-7788 x313
Fax    253-872-7904
http://www.powertechgroup.com


__________________________________________________________________
The NEW Netscape 7.0 browser is now available. Upgrade now! 
http://channels.netscape.com/ns/browsers/download.jsp

Get your own FREE, personal Netscape Mail account today at 
http://webmail.netscape.com/