cPlan Lookup Code Samples

A cPlan can use a lookup to pull in additional columns of data.

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"

}

}

}

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"

}

}

}

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.

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")
  ```          
  //
}

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"

}

}

Suppose you have a cPlan that performs a lookup to bring in a column to use as a key in another lookup. If the second lookup is made part of the first lookup's input block, this works. For example, base data is:

basic test data

The first lookup file is:

first lookup data

The second lookup file is:

second lookup data

The script below shows the lookups within the cPlan.

dive {
  cplan {
    text-input "base.txt"
    
    lookup {
      text-input "lookup1.txt" {
        lookup {
          text-input "lookup2.txt"
          
          key "Other Code" "Code"
          column "Desc"
        }
        
      }
      key "Code" "Code"
      column "Other Code"
      column "Desc"
    }
  }
  
  window {
    dimension "Thing"
    column "Desc"
  }
}

The Dive results shows the lookups performed as expected:

results of the lookup inside the lookup

NOTE: With the second lookup as part of the first lookup's input, all of the records in the first lookup are joined to the second. This would be good if the base table contains many records.

TIP: Lookups in cPlans reading large cBases are slower than if built into the cBase.

See also: Build Code Lookup Samples.