Additional SQL {BATCH-15}

In this option, SQL is used to enhance the validation and LOV of Batch Prompts when entered.


Fields in the option:

Block 1:  LOV SQL

Field Type
&
Length		 Description
SQL Code A8  Enter unique code to identify this Additional SQL.
Description A60 Description for the Additional SQL.
Active A1 Click Yes or No depending on the active status of this Additional SQL.  A record with Active = (N)o will not be used when defining batch prompts.
SQL A2500 This enhances the Record Group that will be used for the LOV by the Batch Prompter (g05app.fmb). The data for the Batch Prompter is defined in {BATCH-2}.

Answers from previous prompts can be used in the SQL by specifying it as ':Report Variable' where Report Variable is the name of the variable as defined for the Prompt in the Report Variable field.

It will contain either the 'WHERE' clause or a complete 'SELECT' statement.


NOTE: No validation is done by the Batch Prompter when doing the LOV. If something is wrong it will return 'No Rows'

A complete example can be seen in {BATCHR-7}
  • SELECT - The SELECT can be entered in full, in which case the Batch Prompter will Ignore the Table Details entered for the Prompt in {BATCH-2}. The select may only return 2 Values. The first will be the data to be returned to the prompt from the LOV. The second is a description. The description can be concatenated fields, e.g.
    SELECT genadrftc, 
                 decode(gentac,'A','Address', 'C','Contact',  'T','Communication') || ': '|| genadrftd
    FROM gen.genadr
    WHERE gensyscrt = ':System_Ref'
     AND gentac = ':tac'
  • WHERE - The where clause that will be added to the SELECT built from the table details in the Prompt {BATCH-2}, TAB - Prompt Information e.g.
    WHERE IAHCYR   = :cyr
    OR         IAHQUAL = ':qual'

Example:

Example of batch-15b1

 


Processing
Rules		  
  No special processing rules.


See Also:


History of Changes

Date System Version By Whom Job Description
18-Apr-2007 v01.0.0.0 Amanda Nell t134792 New manual format.
10-Mar-2008 v01.0.0.0 Vaughn Dumas t134792 System owner proof read.
12-Sep-2008 v01.0.0.0 Charlene van der Schyff t152060 Edit language obtained from proof read language Juliet Gillies.
22-Mar-2022
 v04.1.0.0
 Precious Matshaya
t252681
 If Test document = N, email generated when communication type input parameter has no value on production.  Eliminated by adding UNION in SELECT to add value to list when query returns no value