Cross platform SQL macros

Prophund Reporting has a few SQL macros that allow easier ways to use the same SQL on different servers.

These are used by surrounding the macro with double curly braces.

For those macros with 'item' this is the column or value to be converted to a formatted string.

  • s.current_time() - 'HH24:MI:SS'
  • s.current_date() - 'YYYY-MM-DD'
  • s.datetime(item) - 'YYYY-MM-DD HH24:MI:SS'
  • s.time(item) - 'HH24:MI:SS'
  • s.now() - Database current date and time representation

Example select statement

select '{s.current_date()}' as example_current_date, '{s.current_time()}' as example_current_time

NOTE the use of curly braces and quotes.

JINJA2 overview

JINJA2 is a powerful templating language with more than enough nouse to create complex formatting logic. Refer to the Jinja documentation for all details. When used in Prophund Reporting a selection of environment values are made available in the context for formatting text. Any of these built in parameters can be accessed by surrounding them with double curly braces. Example: {{p_time}}. Or, as a bind variable.

parameter name description
access_group access group dictionary for composing group based queries
p_now date and time, format: “%Y-%m-%d %H:%M:%S”
p_time time, format: “%H:%M:%S”
p_date date, format: “%Y-%m-%d”
host host name from CUSTOM_HOSTNAME environment variable
report_name report name
user_email current user email
user_groups current user groups list
database_type connection database type
connection_name connection name
cluster connection cluster

Bind variables

Any of the parameters, either built in or from report fields, are available to use in the query as bind variables. The usage is: :parameter-name. A colon, then the name of the parameter. Report fields use an _ underscore then a variable version of the field name.

Report name example. Email Name

Becomes. _email_name

Bind variables should be used in preference to comparing to strings as this reduces the ability for incorrect query processing.

Use where name = :_name, rather than where name = '{{_name}}'

Common patterns

Here a few common patterns to use when creating SQL statements using JINJA2.

You'll likely only use the if command, as the others commands are not applicable to SQL.

Optional where when there is an email on a report.

select * from
addresses where true
{% if :_email %}
    and email = :_email
{% endif %}

user has a group name

select * from addresses where true
{% if 'farm' in user_groups }
    and group = 'farm'
{% endif %}

show all group names

select {% for g in user_groups %}'{{g}}',{% endfor %} 12