Oracle Data type | MicroStrategy Data type |
VARCHAR2 | VARCHAR |
NVARCHAR2 | NVARCHAR |
NUMBER | NUMERIC |
FLOAT | FLOAT |
LONG | LONGVARCHAR |
DATE | TIMESTAMP |
BINARY_FLOAT |
|
BINARY_DOUBLE |
|
TIMESTAMP | TIMESTAMP |
TIMESTAMP WITH TIME ZONE |
|
TIMESTAMP WITH LOCAL TIME ZONE |
|
INTERVAL YEAR TO MONTH |
|
INTERVAL DAY TO SECOND |
|
RAW | VARBIN |
LONG RAW | LONGVARBIN |
UROWID |
|
CHAR | CHAR |
NCHAR | NCHAR |
CLOB | LONGVARCHAR |
NCLOB |
|
BLOB | LONGVARBIN |
BFILE |
|
Function | SQL Pattern |
UnionFunction | #<(#>#1#<#0 union #*#>#<)#> |
ExceptFunction | #<(#>#1#<#0 minus #*#>#<)#> |
StdevPFunction | STDDEV_POP(#0#< #*#>) |
ProductFunction | (CASE WHEN SUM(CASE WHEN #0=0 THEN 1 ELSE 0 END) > 0 THEN 0 ELSE POWER(-1,SUM(CASE WHEN #0<0 THEN 1 ELSE 0 END))*EXP(SUM(LN(ABS(CASE WHEN #0<>0 THEN #0 ELSE NULL END)))) END) |
MedianFunction | MEDIAN(#0) |
StdevFunction | STDDEV(#0#< #*#>) |
VarFunction | VARIANCE(#0#< #*#>) |
GeoMeanFunction | EXP(AVG(LN(#0))) |
GreatestFunction | GREATEST(#0#<, #*#>) |
LeastFunction | LEAST(#0#<, #*#>) |
FirstFunction | Min(#0) Keep (dense_rank First order by #1) |
LastFunction | Max(#0) Keep (dense_rank Last order by #1) |
IFOperator | (Case when #0 then #1 else #2 end) |
VarPFunction | VAR_POP(#0#< #*#>) |
Function | SQL Pattern |
DaysBetweenFunction | (TRUNC(#1) - TRUNC(#0)) |
YearEndDateFunction | ADD_MONTHS(TRUNC(#0) - EXTRACT(DAY FROM #0), 13 - EXTRACT(MONTH FROM #0)) |
CurrentDateFunction | TRUNC(CURRENT_DATE) |
DayOfMonthFunction | EXTRACT(DAY FROM #0) |
DayOfWeekFunction | TO_NUMBER(TO_CHAR(#0, 'D')) |
DayOfYearFunction | TO_NUMBER(TO_CHAR(#0, 'DDD')) |
WeekFunction | TO_NUMBER(TO_CHAR(#0+1, 'IW')) |
MonthFunction | EXTRACT(MONTH FROM #0) |
QuarterFunction | TO_NUMBER(TO_CHAR(#0, 'Q')) |
YearFunction | EXTRACT(YEAR FROM #0) |
CurrentDateTimeFunction | LOCALTIMESTAMP |
CurrentTimeFunction | LOCALTIMESTAMP |
HourFunction | TO_NUMBER(TO_CHAR(#0, 'HH24')) |
MinuteFunction | TO_NUMBER(TO_CHAR(#0, 'MI')) |
SecondFunction | TO_NUMBER(TO_CHAR(#0, 'SS')) |
MilliSecondFunction | 0 |
MonthStartDateFunction | TRUNC(ADD_MONTHS((LAST_DAY(#0) + 1), -1)) |
YearStartDateFunction | ADD_MONTHS(TRUNC(#0) - EXTRACT(DAY FROM #0) + 1, 1 - EXTRACT(MONTH FROM #0)) |
AddMonthsFunction | (CASE WHEN EXTRACT(DAY FROM #0) < EXTRACT(DAY FROM ADD_MONTHS(#0, #1)) THEN #0 + #1 * INTERVAL '1' MONTH ELSE ADD_MONTHS(#0, #1) END) |
MonthsBetweenFunction | TRUNC(MONTHS_BETWEEN(#1, #0)) |
AddDaysFunction | (#0 + #1) |
MonthEndDateFunction | TRUNC(LAST_DAY(#0)) |
DateFunction | TRUNC(CAST(#0 AS DATE)) |
Function | SQL Pattern |
RunningStdevPFunction | STDDEV_POP(#0) OVER(#1) |
MovingStdevPFunction | STDDEV_POP(#0) OVER(#1) |
RunningSumFunction | sum(#0) over(#1) |
RunningAvgFunction | avg(#0) over(#1) |
MovingAvgFunction | avg(#0) over(#1) |
MovingSumFunction | sum(#0) over(#1) |
MovingMaxFunction | max(#0) over(#1) |
MovingMinFunction | min(#0) over(#1) |
MovingStdevFunction | stddev(#0) over(#1) |
MovingCountFunction | count(#0) over(#1) |
RunningMaxFunction | max(#0) over(#1) |
RunningMinFunction | min(#0) over(#1) |
RunningStdevFunction | stddev(#0) over(#1) |
RunningCountFunction | count(#0) over(#1) |
FirstInRangeFunction | first_value(#0) over(#1) |
LastInRangeFunction | last_value(#0) over(#1) |
OLAPSumFunction | sum(#0#< #*#>) over ([#P] [#O] [#W]) |
OLAPAvgFunction | avg(#0#< #*#>) over ([#P] [#O] [#W]) |
OLAPCountFunction | count(#0#< #*#>) over ([#P] [#O] [#W]) |
OLAPMaxFunction | max(#0#< #*#>) over ([#P] [#O] [#W]) |
OLAPMinFunction | min(#0#< #*#>) over ([#P] [#O] [#W]) |
LagFunction | lag(#0#<, #*#>) over ([#P] [#O]) |
LeadFunction | lead(#0#<, #*#>) over ([#P] [#O]) |
OLAPRankFunction | rank() over ([#P] [#O]) |
Function | SQL Pattern |
Round2Function | ROUND(#0, #1) |
RankFunction | #0#,#<partition by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0 nulls last) |
AbsFunction | ABS(#0) |
IntersectFunction | #<(#>#1#<#0 intersect #*#>#<)#> |
Int2Function | FLOOR(#0) |
AcosFunction | ACOS(#0) |
AcoshFunction | LN(#0+SQRT(#0-1)*SQRT(#0+1)) |
AsinFunction | ASIN(#0) |
AtanFunction | ATAN(#0) |
Atan2Function | ATAN2(#1, #0) |
AtanhFunction | ((LN(1+#0)-LN(1- #0))/2) |
CeilingFunction | CEIL(#0) |
CosFunction | COS(#0) |
CoshFunction | COSH(#0) |
DegreesFunction | (#0*180/(ASIN(1)*2)) |
ExpFunction | EXP(#0) |
FloorFunction | FLOOR(#0) |
IntFunction | CASE WHEN #0 > 0 THEN TRUNC(#0, 0) ELSE (TRUNC(#0, 0) - 1) END |
LnFunction | LN(#0) |
LogFunction | LOG(#1, #0) |
Log10Function | LOG(10, #0) |
PowerFunction | POWER(#0, #1) |
QuotientFunction | TRUNC((#0)/(CASE WHEN (#1)=0 THEN NULL ELSE (#1) END), 0) |
RadiansFunction | (#0*ASIN(1)*2/180) |
RoundFunction | ROUND(#0) |
SinFunction | SIN(#0) |
SinhFunction | SINH(#0) |
SqrtFunction | SQRT(#0) |
TanFunction | TAN(#0) |
TanhFunction | TANH(#0) |
TruncFunction | TRUNC(#0) |
AsinhFunction | LN(#0+SQRT(POWER(#0,2)+1)) |
ModFunction | MOD(#0, #1) |
Function | SQL Pattern |
KurtosisFunction | ((((SUM(#0- #0+1)*(SUM(#0- #0+1)+1))/((SUM(#0- #0+1)-1)*(SUM(#0- #0+1)-2)*(SUM(#0- #0+1)-3)))*((SUM(POWER(#0,4)))-(4*SUM(POWER(#0,3))*AVG(#0))+(6*SUM(POWER(#0,2))*POWER(AVG(#0),2))-(4*SUM(#0)*POWER(AV |
SkewFunction | ((SUM(#0- #0+1)/((SUM(#0- #0+1)-1)*(SUM(#0- #0+1)-2)))*((SUM(POWER(#0,3)))+(3*SUM(#0)*POWER(AVG(#0),2))-(3*SUM(POWER(#0,2))*AVG(#0))-(SUM(#0- #0+1)*POWER(AVG(#0),3)))/(POWER(STDDEV(#0),3))) |
FisherFunction | (LN((1+#0)/(1- #0))/2) |
InverseFisherFunction | ((EXP(2*#0)-1)/(EXP(2*#0)+1)) |
StandardizeFunction | case when (#2 > 0) then (#0 - #1)/(#2) else NULL end |
CovarianceFunction | COVAR_POP(#0, #1) |
CorrelationFunction | CORR(#0, #1) |
PearsonFunction | (((SUM(#0- #0+#1- #1+1)*SUM(#0*#1))-(SUM(#0)*SUM(#1)))/SQRT(((SUM(#0- #0+#1- #1+1)*SUM(#0*#0))-(SUM(#0)*SUM(#0)))*((SUM(#0- #0+#1- #1+1)*SUM(#1*#1))-(SUM(#1)*SUM(#1))))) |
RSquareFunction | REGR_R2(#0, #1) |
SlopeFunction | REGR_SLOPE(#0, #1) |
InterceptFunction | REGR_INTERCEPT(#0, #1) |
SteYXFunction | SQRT((1/(SUM(#1- #1+#0- #0+1)*(SUM(#1- #1+#0- #0+1)-2)))*((SUM(#1- #1+#0- #0+1)*SUM(#0*#0))-(SUM(#0)*SUM(#0))-((((SUM(#1- #1+#0- #0+1)*SUM(#1*#0))-(SUM(#1)*SUM(#0)))*((SUM(#1- #1+#0- #0+1)*SUM(#1*#0)) |
KurtosisFunction | ((((SUM(#0- #0+1)*(SUM(#0- #0+1)+1))/((SUM(#0- #0+1)-1)*(SUM(#0- #0+1)-2)*(SUM(#0- #0+1)-3)))*((SUM(POWER(#0,4)))-(4*SUM(POWER(#0,3))*AVG(#0))+(6*SUM(POWER(#0,2))*POWER(AVG(#0),2))-(4*SUM(#0)*POWER(AV |
SkewFunction | ((SUM(#0- #0+1)/((SUM(#0- #0+1)-1)*(SUM(#0- #0+1)-2)))*((SUM(POWER(#0,3)))+(3*SUM(#0)*POWER(AVG(#0),2))-(3*SUM(POWER(#0,2))*AVG(#0))-(SUM(#0- #0+1)*POWER(AVG(#0),3)))/(POWER(STDDEV(#0),3))) |
FisherFunction | (LN((1+#0)/(1- #0))/2) |
InverseFisherFunction | ((EXP(2*#0)-1)/(EXP(2*#0)+1)) |
StandardizeFunction | case when (#2 > 0) then (#0 - #1)/(#2) else NULL end |
CovarianceFunction | COVAR_POP(#0, #1) |
CorrelationFunction | CORR(#0, #1) |
PearsonFunction | (((SUM(#0- #0+#1- #1+1)*SUM(#0*#1))-(SUM(#0)*SUM(#1)))/SQRT(((SUM(#0- #0+#1- #1+1)*SUM(#0*#0))-(SUM(#0)*SUM(#0)))*((SUM(#0- #0+#1- #1+1)*SUM(#1*#1))-(SUM(#1)*SUM(#1))))) |
RSquareFunction | REGR_R2(#0, #1) |
SlopeFunction | REGR_SLOPE(#0, #1) |
InterceptFunction | REGR_INTERCEPT(#0, #1) |
SteYXFunction | SQRT((1/(SUM(#1- #1+#0- #0+1)*(SUM(#1- #1+#0- #0+1)-2)))*((SUM(#1- #1+#0- #0+1)*SUM(#0*#0))-(SUM(#0)*SUM(#0))-((((SUM(#1- #1+#0- #0+1)*SUM(#1*#0))-(SUM(#1)*SUM(#0)))*((SUM(#1- #1+#0- #0+1)*SUM(#1*#0)) |
Function | SQL Pattern |
ConcatFunction | (#0#< || #*#>) |
LengthFunction | LENGTH(#0) |
LowerFunction | LOWER(#0) |
LTrimFunction | LTRIM(#0) |
PositionFunction | INSTR(#1, #0) |
RTrimFunction | RTRIM(#0) |
SubStrFunction | SUBSTR(#0, #1, #2) |
InitCapFunction | INITCAP(#0) |
TrimFunction | TRIM(#0) |
RightStrFunction | CASE WHEN LENGTH(TO_CHAR(#0)) < #1 THEN (TO_CHAR(#0)) ELSE SUBSTR((TO_CHAR(#0)), (LENGTH(#0) - #1 + 1)) END |
LeftStrFunction | SUBSTR(#0, 1, #1) |
ConcatBlankFunction | (#0#< || ' ' || #*#>) |
