Sign in
< Back to User Guide Top

Assigning SQL Access Parameters

In the SQL Access editor, you must create the parameters that represent variables for use in your SQL statement.

Each parameter must specify the parameter's data type, the database's driver type, and the parameter's direction (whether the variable is being used in the "where" clause of the SQL statement, getting data out of the database, or both).

In most cases, you will create at least one input parameter and at least one output parameter for each SQL Action. The input parameter(s) must be listed before the output parameter(s).

Clarify currently supports the use of up to 99 parameters.

The parameters are the placeholders (for runtime values) used when writing out the SQL Statement in the SQL Access object's editor. You define the runtime values in the Ruleset or Business Process when you specify input and output values for the SQL Access object.

Note: A Business Process will display the parameter names as you've named them. A Ruleset will display In parameters as you've named them, but all output parameters will display as Output0, Output1, etc.

 

Example Parameters

For example, suppose you want to pull an internal UPC number from your database if a purchase order is from a specific trading partner. Your SQL statement is: Select "Table1"."Column1"."InternalUPC" from "Table1"."Column1" where "Table1"."Column1"."TradingPartnerID" = ${TradingPartnerCode}. You would need two variables:
  • TradingPartnerCode - Used in the SQL statement and the Ruleset to pass in the Trading Partner Code value for comparison
  • InternalUPC - Not used in the SQL statement itself, but used in the Ruleset to map the output UPC to the target document

In this example, the SQL Access editor's Parameters area would look like this:

Name Type Jdbc Type Direction
TradingPartnerCode String * In
InternalUPC String * Out

* Select the value that matches your own database. When typing a parameter in the SQL Statement field, you must type it as ${parameter}.

 

Returning Multiple Output Variables

The screenshot below illustrates how you might return multiple output variables from a Stored Procedure in a SQL Access object.

 

SQLMultipleParams-(1).PNG

Related articles

Comments

0 comments

Please sign in to leave a comment.