52 Administrator’s Guide
BMC Atrium Integration Engine 7.1.00
SQL command syntax (sql|<command>)
Integration Engine runs the select statement (<command>) after the pipe to obtain a
value. This syntax differs from the target SQL command syntax (described in the
following section) in this Integration Engine runs the select statement on the source
database defined for the data exchange.
You can embed field names (
<$field$>) into the select statement. The value of the
field is substituted prior to running the select statement.
For example, in Table 3-5, the value for the Category field (
$category$) is replaced
in the select statement before the statement is processed.
NOTE
If the SQL command returns more than one field or more than one row, Integration
Engine uses the first value and ignores the remaining values.
trunc(real)
(where real is a
constant, field name,
or function)
function|trunc(5.6)
Truncates a real number (5.1 to 5.9
results in value 5):
5
upper(string)
(where string is a
constant, field name,
or function)
function|upper("Alex")
Converts a string to uppercase:
ALEX
weekday(timestamp)
(where timestamp is a
constant, field name,
or function)
function|weekday
($createdate$)
(if $createdate$ is set to
Friday, August 3, 2007
12:15:00 a.m.)
Returns the weekday of the time
stamp (1 to 7 where 1=Sunday):
6
year(timestamp)
(where timestamp is a
constant, field name,
or function)
function|year
($createdate$)
(if $createdate$ is set to
Friday, August 3, 2007
12:15:00 a.m.)
Returns the year portion of the
time stamp as a four-digit year:
2007
Table 3-4: Function syntax examples
Function Example Results
Table 3-5: SQL command syntax example
Example Results
sql|select categoryid
from categories where
name = $category$
Returns category IDs from the categories
table where the name equals the value
obtained for the
$category$ field.
