Using Batch Jobs As Reusable Building Blocks

See Also

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

       ,p.first_name)||' '||p.last_name

       , ...

FROM     report r

       ,person p

       , ...

WHERE    ...

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 ')||

         DECODE(p.formal, 'Y',

         SUBSTR(p.first_name, 1, 1) || '.'

         ,p.first_name) || ' ' || p.last_name

FROM      full_name_inpar

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,

   person p,

   ...

WHERE    ...