The secret
functions in OBIEE?
TO_DATETIME is such a function.
To be fair, you can see this
function in the Administration Tool, but there is no reason not to use it in
analysis directly.
What does it do? It lets you control
the date format of a given date & time string and converts it. No longer
the tyranny of specific formats of the cast function. For example the following
returned the timestamp I needed:
to_datetime ('01
/ 08/2013 21:00 ',' dd / mm / yyyy hh: mi ')
This function Converts string
literals of DateTime format to a DateTime data type. Syntax TO_DATETIME ('string1', 'DateTime_formatting_string)
Where: string1 is the string literal you want to convert DateTime_formatting_string
is the DateTime format you want to use, Such as yyyy. mm.dd hh: mi: ss. For
this argument, Represents year yyyy, mm Represents month, dd Represents day, hh
Represents hour, mi Represents minutes, and ss Represents seconds.
Examples SELECT
TO_DATETIME ('2009-03-03 01:01:00', 'yyyy-mm-dd hh: mi: ss') FROM
Snowflakesales
SELECT TO_DATETIME ('2009 .03.03 01:01:00', 'Yyyy.mm.dd hh:
mi: ss') FROM
Snowflakesales
Snowflakesales
Few
more "secret" functions:
Aggregate Functions
FIRST - selects the first non-null
returned value of the expression argument.
FIRST_PERIOD - selects the first
returned value of the expression argument.
GROUPBYCOLUMN - Used only in
Repository.
GROUPBYLEVEL - Used only in
Repository.
LAST - selects the last non-null
returned value of the expression.
LAST_PERIOD - selects the last
returned value of the expression.
STDDEV_POP - returns the standard
deviation for a set of values Using the
computational formula for population variance and standard deviation.
computational formula for population variance and standard deviation.
Math Functions
EXTRACTBIT - retrieves a bit at a
particular position in an integer. It returns an integer
of 0 or 1 Either Corresponding to the position of the bit.
of 0 or 1 Either Corresponding to the position of the bit.
Conversion Functions
CHOOSE - returns the first item in
the list that the user has permission to see.
INDEXCOL - exists in Admin. Tool.
Can use external information to return the appropriate column for the logged-in
user to see.
Database Functions
EVALUATE_ANALYTIC - exists in Admin.
Tool. This function passes the specified database analytic function with
optional referenced columns as parameters to the back-end data source for
evaluation.
EVALUATE_PREDICATE - exists in
Admin. Tool. Passes the specified database function with optional referenced
columns as parameters to the back-end data source for evaluation. This function
is intended for functions with a return type of Boolean.
Hierarchy Navigation Functions
IDOF -
ISANCESTOR
ISCHILD
ISDESCENDANT
ISLEAF
ISPARENT
ISROOT
PARENT
ISANCESTOR
ISCHILD
ISDESCENDANT
ISLEAF
ISPARENT
ISROOT
PARENT
No comments:
Post a Comment