Selecting Records

Suppose that the Travel Agency wants to be able to delete old tour data, but that they want to be able to choose which destination is involved.

They could write several SQL tasks, each of which carries out the DELETE for a different destination. It is more efficient, however, to write one task, in which a parameter is used that can be given a value at run time.

For example:

DELETE FROM schedtour

WHERE return_date < '01-JAN-96'

AND destination =

SELECT st_dest_var

FROM PAR_DESTINATION)

In this example, the destination for which scheduled tours have to be deleted is supplied by the st_dest_var field in the PAR_DESTINATION input parameter set, which is defined as an external set.

If the job containing this SQL task is called DEL_TOURS, and the company wants to delete only the tours for Great Britain, they can execute this job by means of an icon that has the following command line properties:

runbatch.exe -exe "job(DEL_TOURS,st_dest_var=GREAT BRITAIN)"

In addition, the tour date could also be a parameter, in which case the job could be used on a regular basis to keep the database free of old data.

TIP

When selecting records by means of a job input parameter, it is very handy to add the UPPER function and the (Oracle specific) LIKE operator and wildcards to SQL statements. This has two important advantages:

·	Users do not have to provide case-sensitive parameter values.

·	If appropriate, users are able to provide only a part of a string.

The SQL statement of the example would then read:

DELETE FROM schedtour

WHERE return_date < '01-JAN-96'

AND UPPER(destination) LIKE

(SELECT '%'||UPPER(st_dest_var)||'%'

FROM PAR_DESTINATION)

TIP 2:

You can also use an input parameter set to store global values that need to be available more than once in the batch job.

NOTE:

In a job call, you do not need to provide a value for each of the parameters in the input parameter set. Do not mention parameters that you do not provide a value for. The following is not accepted:

runbatch.exe -exe "job(<job_name>,par1=,par2=<value>)"

Replace this with:

runbatch.exe -exe "job(<job_name>,par2=<value>)"