Preface
RulePoint
RulePoint Concepts
- RulePoint Concepts Overview
- Understanding RulePoint Entities
Using RulePoint
RulePoint Objects
- RulePoint Objects Overview
- State of RulePoint Objects
Working with Topics
Working with Connections
Working with Sources
Working with Responders
Working with Responses
Working with Watchlists
Working with Analytics
- Analytics Overview
- Analytic Types
- Creating an Analytic
- Copying an Analytic
- Editing an Analytic
- Deleting an Analytic
- Viewing Related Objects
- Predefined Analytics
  - abs
  - average
  - ceil
  - charat
  - clean
  - compareto
  - concat
  - confidence
  - correlation
  - count
  - current
  - date
  - datedifference
  - endswith
  - even
  - floor
  - getcurrentdate
  - getdatefrommillis
  - getdateinmillis
  - geodistance
  - geoinbound
  - geoinsidearea
  - geointersect
  - geooutbound
  - geooutsidearea
  - geopassthru
  - indexof
  - integer
  - isblank
  - isholiday
  - isnum
  - istext
  - isweekday
  - isweekend
  - large
  - list2vars
  - lower
  - max
  - min
  - mod
  - mode
  - mround
  - odd
  - parse
  - past
  - percentile
  - power
  - proper
  - range
  - regex
  - replace
  - round
  - rounddown
  - roundup
  - sign
  - small
  - startswith
  - stdev
  - stringequals
  - stringindexof
  - stringlastindexof
  - stringlength
  - substring
  - sum
  - trim
  - truncate
  - upper
  - xpath
- Predefined Operators
  - contains
  - equalsSet
  - has
  - in
  - match
  - notMatch
  - search
  - unique
  - group by
  - distinct
  - unmatched
- Using the distinct, group by, and ummatched Filters
DRQL
Working with Rules
Working with Alerts
Setting Access Controls
Troubleshooting RulePoint Issues
Connecting to an Ultra Messaging Application
Creating an Ultra Messaging JMS Source

User Guide

6.1
- 6.2
- 6.1.2

Back Next

SQL Analytic

The SQL analytic runs an SQL query or command against a target database.

When you run SQL commands and queries as an analytic, you can enrich the data to provide additional information during rule processing that is not available in the event data. An SQL analytic, when used in a rule, requires an SQL connection, so create an SQL connection before you create the SQL analytic.

The following table defines the properties of an SQL Analytic:

Property	Description
Name	The name of the SQL analytic. This must be a unique name.
Description	(Optional) The description of the analytic.
Type	Select SQL Analytic .
Connection	Select the connection type from the list of available connections.
SQL Query	The SQL query to run against the target database. Use question marks to identify the substitution parameters in the SQL statement that will be replaced by the data values provided in the rule.
Condition Evaluation Required	Specifies whether the analytic is part of conditional evaluation within a rule. Set to 'false' if the result of the analytic can be used only for enrichment. The default value is True.
Cache Duration	Time period, in seconds, for which the result of the analytic is to be cached.

Example

Consider a transaction based SQL Service that you can use to calculate the total number of transactions for a particular merchant.

The following data represents the information that you would provide when creating an SQL analytic called TransactionCount to use as an analytic in the rule:

Name. TransactionCount

Description. Returns the total number of transactions made with a specific merchant, with the merchant name and purchase date as the input parameters.

Connection. Choose the created SQL connection.

SQL Query.

SELECT count(*) FROM creditcard.purchase where Merchant_Name=? and Purchase_Date < ?

Condition Evaluation Required: Select True and False.

Cache Duration: Total number of milliseconds for which the analytic result will be cached.

The following rule uses the TransactionCount SQL analytic to determine the total number of transactions for a particular merchant before a specified date. If the TransactionCount is greater than 40 transactions, then respond appropriately.

WHEN 1 transaction WITH TransactionCount( Merchant_Name , Purchase_Date ) as result > 40 then response with body = “${result}”

The order of arguments of the analytic must match the position of the question marks.

Rename Saved Search

Table of Contents

User Guide

User Guide

SQL Analytic

SQL Analytic

Example