| Using RPG in SQL |
 |
 |
 |
| Programming - RPG | |||||||||||||||
| Written by Paul Tuohy | |||||||||||||||
| Tuesday, 18 September 2007 18:00 | |||||||||||||||
|
In this article, I will look at using a simple RPG subprocedure that converts a numeric field containing a date to an actual date field. The RPG SideFigure 1 shows the DDS for a simple table containing Employee ID, Name, Date Joined, and Date of Birth. The Date Joined and Date of Birth fields are defined as packed numeric data types and contain dates in ISO (YYYYMMDD) format. If there is a need to perform any sort of "date math" (e.g., calculating a person's current age or the age they were when they joined the company), there is no problem converting the dates to proper date fields in an RPG program.
Figure 1: This is the DDS of a simple table. To make the date conversion process even easier in RPG programs, I have written a ConvertToDate subprocedure, shown in Figure 2, which converts numeric fields to date fields. The ConvertToDate subprocedure accepts a single parameter (which should be a numeric field containing a date in ISO format) and returns either the ISO date equivalent of the passed parameter (if it contains a valid date) or today's date (if the passed parameter is not a valid date). The module containing the ConvertToDate subprocedure is in a service program named DATES.
Figure 2: The ConvertToDate subprocedure converts numeric fields to date fields. You can use the ConvertToDate subprocedure anywhere you need to treat a numeric date field (with a date in ISO format) as a proper date field. For example, using the database described in Figure 1, you would use the following code to calculate the age of an employee when he or she joined the company: Age = %Diff( ConvertToDate(Joined) : ConvertToDate(Birth) : *Y);
The use of ConvertToDate in programs ensures that a proper date is always used and takes care of the unlikely instance that an invalid date might exist in the database. Of course, you could add extra processing to log any errors, halt processing, or return a different default date instead of today's date. But how can you use the ConvertToDate subprocedure in SQL? Define an SQL Function in iSeries NavigatorJust as RPG has BIFs, SQL has scalar functions. And just as RPG allows you to define your own BIFs by writing subprocedures, SQL allows you to define your own scalar functions, referred to as user-defined functions (UDFs). If you have an urge to jump into the deep end, you can attempt to define an SQL function using a CREATE FUNCTION command in SQL, but, for the first attempt at least, it is a lot easier to use the wizard provided in iSeries Navigator. I will return to the CREATE FUNCTION command later in this article. To define a function using iSeries Navigator, you must add the schema (library) that will contain the function to the list of schemas. Expand Databases -> DatabaseName -> Schemas. If the required schema is not in the list, right-click on Schemas and select the option to Select Schemas to Display and add the schema to the list. To define a function, expand the required schema, right-click on Functions, and select New -> External from the pop-up menu, as shown in Figure 3. An external SQL function indicates that the SQL function is a "wrapper" to call an external program or subprocedure. Figure 3: An "external" SQL function calls an external program or subprocedure. (Click images to enlarge.)
The wizard to define an external SQL function consists of three tabs: General, Parameters, and External Program. Let's look at the contents of the three tabs in detail, starting with the General tab, shown in Figure 4, which identifies the function and the value (or values) it returns and provides details relating to the SQL environment for the function. Figure 4: The General tab defines a function.
The Parameters tab, shown in Figure 5, identifies the parameters that are passed to the function. Figure 5: The Parameters tab identifies the parameters passed to the function.
The External Program tab, shown in Figure 6, identifies the program or subprocedure that is called by the function. The function may be a program or a Java method. For the purposes of this article, I'll discuss only the program. Figure 6: The External Program tab identifies the program or subprocedure called by the function.
Now click on the OK button to create the ConvertToDate Function. Create FunctionEarlier in the article, I mentioned that you could define a function using the CREATE FUNCTION command in SQL. To get an idea of the format of the SQL CREATE FUNCTION command, right-click on an existing SQL function in iSeries Navigator and select Generate SQL from the pop-up menu. Figure 7 shows the SQL CREATE FUNCTION command that would be used to create the ConvertToDate function defined earlier.
Figure 7: Define a function with CREATE FUNCTION. When defining multiple functions that are somewhat similar, it is often easier to define the first function using iSeries Navigator and then define the remaining functions using the first function as a template; simply generate the SQL, change the relevnt definitions, and run the commands. Now let's look at a couple of examples of using the newly defined function. Using the Function with SELECTYou can use your own user-defined functions in almost any place you can use an SQL scalar function. For example, Figure 8 shows an SQL SELECT statement that uses the ConvertToDate function and the corresponding result of running the statement. The ConvertToDate function is used to represent the Birth and Joined columns as proper dates and used again in the calculation of the Age When Joined column.
Figure 8: Use the function in a SELECT statement. (Note to self: Stop using your date of birth in these examples). Using the Function in a ViewYou can make life even easier by defining a view that already does all the hard work. Figure 9 shows the definition of the view EMPLOYEEV1, which contains the definition of columns corresponding to those defined in the SELECT statement shown in Figure 8.
Figure 9: Use the function in the definition of a view. Now simply selecting all columns from the view EMPLOYEEV1 generates the same result set shown back in Figure 8. Do you think your users might find this useful when they are using QUERY? A Last Word...The ability to use existing RPG programs and subprocedures as functions within SQL can offer some intriguing possibilities in that they can provide a means of using existing application logic to provide column values. Yet another tool to add to your ever-expanding utility belt! | |||||||||||||||
|
|||||||||||||||
[size="1">Code[/size>