Decision variables and placeholders

When you call a Decision, you can pass values to it as the second, third... argument of the ActionDecision() action:

ActionDecision( MY_DECISION, 'Table1', 776 )

Each value passed corresponds to a numbered placeholder ( :1, :2 ...) in the decision's Decision SQL. Before the SQL statement is executed, each :1 placeholder is substituted by the value passed in first place (in the example, the Table1 string), each :2 placeholder by the next value (in the example, the number 776), and so on. See the ActionDecision() help topic for details on this mechanism.

After a decision's Decision SQL is executed, the output values from the SELECT clause may be passed to the Yes Action and No Action scripts. Every time the :1 placeholder appears in these scripts, it is substituted by the first output value from the SELECT list for the retrieved row currently being processed. Each :2 placeholder is substituted by the second output value from the SELECT list, and so on.

In the Yes Action and No Action scripts, instead of picking up an output value from the Decision SQL in a numbered placeholder (:1, :2...), you can take the intermediary step of first storing the value in a global variable. The ResourceFileUpdate() action allows you to do this. The syntax for storing a value in this way is:

ResourceFileUpdate( 'variable-name : variable-value' )

The stored value is now named and may be accessed repeatedly by name until it is cleared or reset. The syntax for accessing a stored value is:

:"variable"

One special reason for using global variables is that you need them to substitue values in SqlScript() statements. See the next section in this help topic for details.

The general pattern for catching a value in a global variable is:

(Decision SQL:)

SELECT    value1

,         value2 ...

(Yes Action:)

ResourceFileUpdate( 'name1:' ) || :1

ResourceFileUpdate( 'name2:' ) || :2

... :"name1" ...

... :"name2" ...

An example of this pattern is:

(Decision SQL:)

SELECT  T_RDMI_AUTOMATION_SERVER

,       T_RDMI_JAVA_COMPONENT,

FROM    T_RDMI_COMPONENT 

WHERE   COMPONENT_NAME= :1

(Yes Action:)

ResourceFileUpdate( 'global.automation:' || :1 )

ResourceFileUpdate( 'global.java:' || :2 )

SqlScript( 

  UPDATE  appcomponent 

  SET     processed = 'Y'

  WHERE   autom = :"global.automation"

  AND     java_class = :"global.automation"

)

In the Yes Action and No Action scripts, you can perform data manipulation. The SqlScript() action allows you to do this, but if you want to substitute variables in the SQL statement before executing it, you need to cast them in global variables because substitution placeholders of type :1, :2 will not be resolved by SqlScript():

(Decision SQL:)

SELECT  :1

,       :2

,       typename  

FROM    t_e_type 

WHERE   application_id = :2 

AND     is_class ='Y' 

AND     typename IN

(

   SELECT page_name 

   FROM   t_page_in_page_set pps

   ,      t_e_type tt

   WHERE  pps.page_set_name = :1

   AND    tt.id = :2 

   AND    pps.application =  tt.typename

)

(Yes Action:)

ResourceFileUpdate('windowToList:' || :3)

SqlScript(

  INSERT INTO windows_in_set( name ) 

  SELECT :windowToList 

  FROM   dual

  WHERE NOT EXISTS(

    SELECT '' 

    FROM   windows_in_set 

    WHERE  name = :windowToList

  )

)