Using Batch Jobs As Reusable Building Blocks
Sometimes, the same piece of SQL code can be found in many different places. For example, the generation of the full name of a person for a report could look something like this:
SELECT DECODE(p.gender, 'M', 'Mister ', 'F', 'Miss ') ||
DECODE(r.formal, 'Y', SUBSTR(p.first_name, 1, 1) || '.'
FROM report r
When this piece of SQL needs to be used in several places, there are several ways to avoid duplication of the code, and therefore making maintenance on it less difficult and expensive. For example, you can add a calculated full-name field to the PERSON table, which can then be updated by using a constraint. Another way is to create a logical view that performs this calculation and then re-use the logical view.
An alternative way is to define a batch job: Create a job with four input parameters: gender, formal, first_name and last_name. The job has one output parameter: full_name.
Define a SQL task with SQL statement:
INSERT INTO full_name_outpar( full_name )
SELECT DECODE(p.gender, 'M', 'Mister ', 'F', 'Miss ')||
SUBSTR(p.first_name, 1, 1) || '.'
,p.first_name) || ' ' || p.last_name
All SQL statements that require this full name can then be changed into:
SELECT BatchRunner.FullName(p.gender as gender,
r.formal as formal,
p.first_name as first_name,
p. last_name as last_name
FROM report r,