Using Timestamps as Unique Keys

August 14, 2002 Timothy Prickett Morgan

Hey, Ted:

In response to your article in the July 17 Midrange Guru, OS/400 Edition, the short answer is this: You can’t get there from here.

I posed this same question to IBM several months back and got some ridiculous suggestion about writing a record to a dummy file with a timestamp in it, then read it back and use the timestamp of that record.  Huh?

I ended up using the QWCCVTDT API. This API will return the time to milliseconds. This was close enough.

I was disappointed to discover that there is no way to populate the

Make an SQL UDF Return Null

August 14, 2002 Timothy Prickett Morgan

Hey, Ted:

In the October 19, 2001 Midrange Guru, OS/400 Edition, you showed how to use an RPG subprocedure in a service program as an SQL user-defined function. Can you tell me how to write a similar function that can return a null value?

— Marty

The key to returning a null value is in the parameter style (the parameter-passing convention) that you use. I like the SQL convention, but there are others that will work.

The SQL convention passes the following parameters:

• All of the input parameters specified in the create function SQL command
• The output parameter

SQL Functions for Mashing Characters

August 14, 2002 Timothy Prickett Morgan

Hey, Howard:

I have a field that has embedded ‘-‘ characters that I want to remove.

For example, if the field contains 01-111-3345, I want to return it as 001113345.

However, the translate function does not accept an empty string.

Can you tell me how to remove characters from a string in SQL?

— Mike

Unfortunately, there is not an easy way to do this when you have more than one occurrence of the character in a string.

However, a user-defined function using the Persistent Stored Modules (PSM), feature of DB2 is one way to get this done:

CREATE function