CoginitiScript Constants

In SQL, a constant value does not change during the execution of a query or statement. It is a fixed value that can be used in expressions, conditions, and other parts of a SQL statement. In Coginiti, you can use #+const to declare a constant and assign values to them.

CoginitiScript constants dynamically insert values or variables into a string or text-based expression as an interpolation expression. It allows you to construct a string that includes placeholders for values, which are then replaced with actual values at runtime.

CoginitiScript supports the following built-in data types:

• Integers – 64-bit signed integer values
• Floats – 64-bit deecimal value
• Strings – String (varchar/text) values
• Keywords – Symbolic identifiers that evaluate themselves. They provide very fast equality tests and recommend being used as keys in Maps. Examples: :name, :doc
• Lists – list of integers, floats, or strings
• Maps – Key/value pairs stored as a hash map. Values may be integers, floats, or strings.

CoginitiScript tags are wrapped in double curly brackets, and may reference CoginitiScript blocks, macros, or constants.

Here’s an example of how you would declare and use constants in Coginiti:

#+const
  num_users = 100;
  email_domain = "coginiti.co";
  fields = ["name", "email", "postal_code"]; 
  maps = { :key "value", :color "blue" };
#+end

select 
     {{num_users}} as int
  , '{{ email_domain }}' as string
  , '{{ fields[1] }}' as list
  , '{{ maps[:color] }}' as hash
;

This CognitiScript defines four different constants, which are then interpolated into the SQL statement at runtime.  Constants are most often used for thresholds, settings, and slowly-changing parameters common across an entire team or organization.  They can also be used when performing an analysis over a number of scenarios, where each scenario is configured with constants.  Consider the following example, which defines a segment of customers which have spent either $500 so far this year, or $100 in the past 30 days.  These thresholds could be defined in one place and used throughout a number of SQL assets.

#+const
  purchases_ytd_threshold = 500;
  purchases_recent_threshold = 100; 
  recent_days_threshold = 30;
#+end

select 
  {{ last_visit_days_threshold }} as last_visit_days_threshold
  , {{ purchases_ytd_threshold }} as purchases_ytd_threshold
  , {{ purchases_recent_threshold }} as purchases_recent_threshold
  , c.customer_id
  , sum(iff(s.date > date_trunc(year, current_date), s.total, 0)) as purchases_ytd
  , sum(iff(s.date >= current_date - {{last_visit_days_threshold}}, s.total, 0)) as purchases_recent
from
  customer as c
  join visits as v on v.customer_id = c.customer_id
  join sales as s on s.customer_id = c.customer_id
group by
  c.customer_id
having
  purchases_recent >= {{ purchases_recent_threshold }} 
  OR purchases_ytd >= {{ purchases_ytd_threshold }}
;

Using constants in parameterized SQL makes the script easier to read and modify, as the thresholds are defined in one place and can be easily changed without affecting the rest of the script.

Since constants are interpolated into SQL before execution by the database platform, constants can contain any value that results in valid SQL.  For example, you could use a constant to store an aggregate function name, an a whole where clause:

#+const
  agg_func = "MAX";
  where_clause = "WHERE date > CURRENT_DATE - 7"
#+end

select 
    c.customer_id
  , {{ agg_func }}(s.sale_amount) as sales_{{ agg_func}}
from
  customer as c
  join sales as s on s.customer_id = c.customer_id
group by 
  c.customer_id
{{ where_clause }}
;

Generally, CoginitiScript macros are more suitable for entire SQL clauses.