A cPlan can use a lookup to pull in additional columns of data.
Text Lookup
The following cPlan includes a column from a lookup against a flat text file.
cplan {
cbase-input "import_export.cbase" {
lookup {
text-input "lookup_ratecode.txt"
key "Rate Code" "Rate Code"
column "Rate Code Description"
}
}
}
ODBC Lookup
The following cPlan includes a lookup against a database for retrieving live data. The lookup results are refreshed at most once a minute from the database table. The refresh interval is configured with the stale-after tag to define how long, in seconds, to cache the result of the query; after that time when the user does a new dive operation, the database is queried again.
cplan {
cbase-input "import_export.cbase" {
lookup {
odbc-input {
dsn "TestDB"
query "SELECT \"Rate Code\", \"Rate Code Description\" FROM RC_table"
stale-after 60
}
key "Rate Code" "Rate Code"
column "Rate Code Description"
}
}
}
ODBC Lookup with SQL 'Where' Conditions
If you need to provide a parameter, such as a selection from a QuickView, to your cPlan, you might use the following general syntax if your ODBC driver supports parameterized queries:
cplan {
take-parameter "PROJECT" {
default "<None>"
}
cbase-input "Sales.cbase" {
lookup {
odbc-input {
dsn "TestDB"
query """
SELECT "ACTIVITY KEY", "PROJECT"
FROM "Test Table"
WHERE "PROJECT" = ?""" {
bind "$(PROJECT)"
}
stale-after 60
}
key "ACTIVITY KEY" "ACTIVITY KEY"
column "PROJECT"
}
}
}
Note the use of the bind tag as a sub-block under query and the use of a question mark (?) placeholder in the query string for the parameter. Multiple parameter bindings are supported—place the multiple bind's in the same order that the placeholder ?'s appear in the query.
If the ODBC driver does NOT support parameterized queries, the query part of the cPlan is constructed with an expression. Please note that the use of multiple double-quotation marks and backslashes can make this query more difficult to read and maintain. For example:
query ```
"SELECT \\"ACTIVITY KEY\\", \\"PROJECT\\"
FROM \\"Test Table\\"
WHERE \\"PROJECT\\" = '" + replace(param("PROJECT"), "'", "''") + "'"
```
NOTE:
- A marker or Dive using this cPlan can include a QuickView for the parameter. Anytime the QuickView selection changes, the lookup content updates.
- If users have the same effective access control rules, they share cache entries for such a cPlan, even if their QuickView selections vary.
- When querying the database, in addition to processing it for return to the user, Spectre caches the results for a specified time. The stale-after setting controls how long the cache is fresh. Identical queries in that period of time return the cached values instead of querying the database. After that period elapses, Spectre queries the database again.
Another code sample
In this query block, the SQL bind feature is used to bind the evaluation of the user_login to the Spectre expression `current_user()`:
cplan {
cbase-input "/cbases/edit_goals_salesreps.cbase" {
}
lookup {
odbc-input {
dsn "DIPA_DEV"
query """
SELECT "user_login", "user_level"
FROM "t_dipa_user_accounts"
WHERE "user_login" = ?
""" {
bind `current_user()`
}
stale-after 60
}
column "user_login"
column "user_level"
}
// calcs
dimension "user tier" `substr(value("user_level"), 10,6)`
//Filters
filter ```
value("user tier") > value("salesrep_tier")
```
//
}
Dive Lookup
If you are using dive-input for a lookup, you cannot pass a parameter directly, but you can use an inline Dive instead. For example:
cplan {
cbase-input "my-input.cbase"
lookup {
dive-input {
dive {
cplan "my.cplan"
window {
dimension "Code"
// Use the "Description Column" parameter to pick a type of description
column "$(Description Column)"
// Normalize by renaming the description column to "Description"
rename "$(Description Column)" "Description"
}
}
}
key "Code" "Code"
}
}
Chained Lookups
Suppose you have a cPlan that performs a lookup to bring in a column to use as a key in another lookup. Position the lookups one after the other in the cPlan.
For example, base data is:
The first lookup file is:
The second lookup file is:
The script below shows the lookups within the cPlan.
dive {
cplan {
text-input "base.txt"
// Map "Code" to "Other Code"
lookup {
text-input "lookup1.txt"
key "Code" "Code"
column "Other Code"
}
// Now map "Other Code" to "Desc"
lookup {
text-input "lookup2.txt"
key "Other Code" "Code"
column "Desc"
}
}
window {
dimension "Thing"
column "Desc"
}
}
The Dive results shows the lookups performed as expected:
TIP: Lookups in cPlans reading large cBases are slower than if built into the cBase.
See also: Build Code Lookup Samples.