Insight (OBIEE) Tips | Commonly used SQL expressions
This document outlines some useful Insight expressions that we find our clients using. These are just examples and can used in many different situations.
This helps users filter based on dynamic dates.
Example: If you want to see all Email Unsubscribes in the past 90 days, you would
- Create a filter on Email Unsubscribe Date
- Choose the operator “is greater than”
- From the “Add More Options” drop down, select SQL Expression
- A SQL Expression text box would appear
- Enter the expression TIMESTAMPADD(SQL_TSI_DAY, -90, CURRENT_DATE) then click OK
The syntax of this SQL expression is TIMESTAMPADD(interval, count, timestamp)
- TIMESTAMPADD: Returns the timestamp calculated by adding count intervals of type interval to timestamp
- Specified intervals can be:
- Count: An expression that adds up to an integer.
- Timestamp: Any expression that evaluates to a valid timestamp.
First day of current month:
TIMESTAMPADD(SQL_TSI_DAY, -DAYOFMONTH(CURRENT_DATE) +1 , CURRENT_DATE)
First day of month, 12 moths ago:
TIMESTAMPADD(SQL_TSI_MONTH, -12, TIMESTAMPADD(SQL_TSI_DAY, -DAYOFMONTH(CURRENT_DATE) + 1 , CURRENT_DATE))
Returns the value of an expression converted to a specified data type.
CAST(expression AS datatype)
Changing a date to a string:
CAST(Email Unsubscribe Date AS CHAR)
This converts the date “4/1/2018 10:00:00 PM” into text “Apr 1 2018 10:00PM”.
Changing Boolean to true of false:
CAST(Opt-In field AS CHAR)
Ensure that you are using compatible data types as not all are compatible, please refer to:
This function calculates the running sum based on records encountered so far.
NumExpr is any expression that evaluates to a numeric value.
If you are in the process of IP warming and want to keep track of bouncebacks on a daily basis. You would modify the report Email Bounceback Overview. Make sure that the Bounceback back date is sorted in an ascending fashion. Then add another Total Bouncebacks column and edit its formula:
Click the function button:
Then select Functions>Running Aggregate>RSUM.
Notice the expression now becomes:
This column will give you the running sum of your total bouncebacks day by day:
If you would like to see when you have had a certain number of bouncebacks in a campaign, this report using the RSUM function would be useful.
I hope this helps, Let me know if you have any questions in the comments.