Supported functions in SQL
Database native SQL does not provide equivalent functionality for all the functions that you can use in job designs.
The following table lists functions that can be used in a Transformer stage and states whether they can be reproduced in SQL.
If
you use any of these unsupported functions in any expression in a
Transformer stage, then the processing contained in that Transformer
stage cannot be pushed into target or source SQL. If you want to
use these items in your job, segregate them into a separate Transformer
stage which will not be pushed into a database source or target.
| Function | Supported in Teradata SQL | Supported in IBM® DB2® SQL | Supported in Netezza | Supported in Oracle SQL | Comment |
|---|---|---|---|---|---|
| Abs(Number) | Yes | Yes | Yes | Yes | |
| Acos(Number) | Yes | Yes | Yes | Yes | |
| Alnum(String) | No | No | Yes | No | no SQL equivalent |
| Alpha(String) | No | No | Yes | No | no SQL equivalent |
| AsDouble(Number) | Yes | Yes | Yes | Yes | |
| AsFloat(Number) | Yes | Yes | Yes | Yes | |
| ASin(Number) | Yes | Yes | Yes | Yes | |
| AsInteger(Number) | Yes | Yes | Yes | Yes | |
| Atan(Number) | Yes | Yes | Yes | Yes | |
| BitAnd(Num1,Num2) | No | No | Yes | Yes | |
| BitCompress(String) | No | No | No | No | no SQL equivalent |
| BitExpand(Number) | No | No | No | No | no SQL equivalent |
| BitOr(Num1,Num2) | No | No | Yes | No | |
| Ceil(Number) | Yes | Yes | Yes | Yes | |
| Char(Number) | Yes | No | Yes | Yes | |
| CompactWhiteSpace(String) | No | No | No | No | no SQL equivalent |
| Compare(String1,String2) | Yes | Yes | Yes | Yes | |
| Compare(String1,String2,"L") | Yes | Yes | Yes | Yes | |
| Compare(String1,String2,"R") | No | No | No | Yes | |
| CompareNoCase(String1,String2) | Yes | Yes | Yes | Yes | |
CompareNum(String1,String2,
Length) |
Yes | Yes | Yes | Yes | |
CompareNumNoCase(String1,String2,
Length) |
Yes | Yes | Yes | Yes | |
| Convert(FromList,ToList,String) | No | Yes | Yes | Yes | no Teradata SQL equivalent |
| Cos(Number) | Yes | Yes | Yes | Yes | |
| Cosh(Number) | Yes | Yes | Yes | Yes | |
| Count(String1,String2) | No | No | No | No | no SQL equivalent |
| CurrentDate() | Yes | Yes | Yes | Yes | |
| CurrentTime() | Yes | Yes | Yes | Yes | |
| CurrentTimeMS() | Yes | No | No | Yes | |
| CurrentTimestamp() | Yes | Yes | Yes | Yes | |
| CurrentTimestampMS() | Yes | Yes | No | Yes | |
| DateFromDaysSince(Days,Date) | Yes | Yes | Yes | Yes | |
| DateFromDaysSince(Days) | No | No | Yes | Yes | |
| DateFromJulianDay(JulianDayNumber) | Yes | Yes | Yes | Yes | |
| DateToString(Date) | Yes | Yes | Yes | Yes | |
| DateToString(Date,Format) | Yes | Yes | Yes | Yes | DB2 formats limited to "%DD.%MM.%YYYY", "%MM-%DD-%YYYY", "%MM/%DD/%YYYY", "%YYYY-%MM-%DD" |
DaysSinceFromDate(GivenDate,
SourceDate) |
Yes | Yes | Yes | Yes | |
| DCount(String,Delimiter) | No | No | No | No | no SQL equivalent |
| DecimalToDecimal(Number) | Yes | No | Yes | Yes | DB2 default rounding does not match DataStage® default rounding |
| DecimalToDecimal(Number,Rounding) | Partial | Partial | Partial | Partial | for DB2, Rounding="trunc_zero" only. For Teradata, Netezza, and Oracle, Rounding="round_inf" only |
| DecimalToDFloat(Number) | Yes | Yes | Yes | Yes | |
| DecimalToDFloat(Number,"fix_zero") | Yes | Yes | Yes | Yes | |
| DecimalToString(Number) | Yes | Yes | Yes | Yes | |
| DecimalToString(Number,"fix_zero") | Yes | Yes | Yes | Yes | |
| DFloatToDecimal(Number) | Yes | No | Yes | Yes | DB2 default rounding does not match DataStage default rounding |
| DFloatToDecimal(Number,Rounding) | Partial | Partial | Partial | Partial | for DB2, Rounding="trunc_zero" only. For Teradata, Netezza, and Oracle, Rounding="round_inf" only |
| DFloatToStringNoExp(Number,scale) | No | No | No | No | no SQL equivalent |
| Div(Number,Divisor) | Yes | Yes | Yes | Yes | |
| DownCase(String) | Yes | Yes | Yes | Yes | |
| DQuote(String) | Yes | Yes | Yes | Yes | |
| DSJobStartDate() | No | No | No | No | no SQL equivalent |
| DSJobStartTime() | No | No | No | No | no SQL equivalent |
| DSJobStartTimestamp() | No | No | No | No | no SQL equivalent |
| ElementAt(Arg) | No | No | No | No | no SQL equivalent |
| Exp(Number) | Yes | Yes | Yes | Yes | |
| Fabs(Number) | Yes | Yes | Yes | Yes | |
| Field(String,Delimiter,Occurrence) | Partial | Partial | Yes | Yes | supported only for Occurrence=1 |
| Field(String,Delimiter,Occurrence, Number) | Partial | Partial | No | Partial | supported only for Occurrence=1 and Number=1 |
| Floor(Number) | Yes | Yes | Yes | Yes | |
| GetEnvironment(EnvVar) | No | No | No | No | no SQL equivalent |
| HoursFromTime(Time) | Yes | Yes | Yes | Yes | |
| Index(String1,String2,Number) | Partial | Partial | Yes | Yes | For Teradata and DB2 supported only for Number=1 |
| IsNotNull(Arg) | Yes | Yes | Yes | Yes | |
| IsNull(Arg) | Yes | Yes | Yes | Yes | |
| isValid(Type,Arg) | Partial | No | No | No | for Teradata, the following Type arguments are supported: "date", "dfloat", "int16", "int32", "int64", "int8", "raw", "sfloat", "string", "time", "uint16", "uint32", "uint64", "unit8", "timestamp" |
| JulianDayFromDate(Date) | Yes | Yes | Yes | Yes | |
| Ldexp(Mantissa,Exponent) | No | No | No | No | no SQL equivalent |
| Left(String,Length) | Yes | Yes | Yes | Yes | |
| Len(String) | Yes | Yes | Yes | Yes | |
| Llabs(Number) | No | Yes | Yes | Yes | no Teradata SQL equivalent |
| Ln(Number) | Yes | Yes | Yes | Yes | |
| Log10(Number) | Yes | Yes | Yes | Yes | |
| MantissaFromDecimal(Number) | No | No | No | No | no SQL equivalent |
| MantissaFromDFloat(Number) | No | No | No | No | no SQL equivalent |
| Max(Num) | Yes | Yes | No | Yes | |
| Max(Num1,Num2) | Yes | Yes | Yes | Yes | |
| Mean(Num) | Yes | Yes | No | Yes | |
| Mean(Num1,Num2) | Yes | Yes | No | Yes | |
| MicrosecondsFromTime(Time) | Yes | No | Yes | Yes | DB2 TIME type does not support microseconds |
| Min(Num) | Yes | Yes | No | Yes | |
| Min(Num1,Num2) | Yes | Yes | Yes | Yes | |
| MinutesFromTime(Time) | Yes | Yes | Yes | Yes | |
| Mod(Num1,Num2) | Yes | Yes | Yes | No | |
| MonthDayFromDate(Date) | Yes | Yes | Yes | Yes | |
| MonthFromDate(Date) | Yes | Yes | Yes | Yes | |
| Neg(Number) | Yes | Yes | Yes | Yes | |
| NextSKChain(Vector) | No | No | No | No | no SQL equivalent |
| NextSurrogateKey() | No | No | No | No | no SQL equivalent |
| NextWeekdayFromDate(Date,Weekday) | No | No | Yes | Yes | |
| NullToEmpty(Arg) | Yes | Yes | Yes | Yes | |
| NullToValue(Arg1,Arg2) | Yes | Yes | Yes | No | |
| NullToZero(Arg) | Yes | Yes | Yes | No | |
| Num(Arg) | No | No | Yes | No | |
| PadString(String,PadString) | No | No | No | No | no SQL equivalent |
| PadString(String,PadString,Length) | No | Yes | Yes | Yes | no Teradata SQL equivalent |
PreviousWeekdayFromDate(Date,
Weekday) |
No | No | Yes | Yes | |
| PrevSKChain(Vector) | No | No | No | No | no SQL equivalent |
| Pwr(Num1,Num2) | Yes | Yes | Yes | Yes | |
| Rand() | No | No | No | Yes | produces unsigned 32-bit values; SQL INTEGER is signed 32-bit |
| Random() | No | No | No | Yes | produces unsigned 32-bit values; SQL INTEGER is signed 32-bit |
| RawLength(Raw) | Yes | Yes | Yes | Yes | |
| Right(String,Length) | Yes | Yes | Yes | Yes | |
| SecondsFromTime(Time) | Yes | Yes | Yes | Yes | |
| SecondsSinceFromTimestamp (Timestamp1,Timestamp2) | No | Yes | Yes | No | No Teradata or Oracle SQL equivalent |
| SetBit(Number) | No | No | No | No | no SQL equivalent |
| SetNull() | Yes | Yes | Yes | Yes | |
| Seq(Character) | No | Yes | Yes | Yes | |
| Sin(Number) | Yes | Yes | Yes | Yes | |
| Sinh(Number) | Yes | Yes | Yes | Yes | |
| Soundex(String) | Yes | Yes | No | Yes | |
| Space(Number) | Partial | Yes | Yes | Yes | For Teradata, limited to N <= 254 |
| Sqrt(Number) | Yes | Yes | Yes | Yes | |
| SQuote(String) | Yes | Yes | Yes | Yes | |
| Str(String,RepeatCount) | Partial | Yes | Yes | Yes | For Teradata, RepeatCount <= 5 |
| StringToDate(String) | Yes | Yes | Yes | Yes | |
| StringToDate(String,Format) | Yes | Partial | Yes | Yes | For DB2, Format limited to "%MM/%DD/%YYYY" |
| StringToDecimal(String) | Yes | No | Yes | Yes | |
| StringToDecimal(String,Format) | Partial | Partial | Partial | Partial | For DB2, Format can only be “trunc_zero”; for Teradata, Netezza, and Oracle, Format can only be “round_inf” |
| StringToRaw(String) | Yes | No | No | Yes | |
| StringToTime(String) | Yes | Yes | Yes | Yes | |
| StringToTime(String,Format) | Yes | Partial | Yes | No | For DB2, Format limited to "%HH:%NN:%SS" |
| StringToTimestamp(String) | Yes | Yes | Yes | Yes | |
| StringToTimestamp(String,Format) | Yes | Partial | Yes | Yes | For DB2, Format limited to "%YYYY-%MM-%DD %HH-%NN-%SS" |
| StringToUstring(String) | No | No | No | No | no SQL equivalent |
| StringToUstring(String,map_name) | No | No | No | No | no SQL equivalent |
| StripWhiteSpace(String) | No | No | No | Yes | |
| Substring(String,SubString,Length) | Yes | Yes | Yes | Yes | |
| Tan(Number) | Yes | Yes | Yes | Yes | |
| Tanh(Number) | Yes | Yes | Yes | Yes | |
| TimeDate() | Yes | Yes | Yes | Yes | |
| TimeFromMidnightSeconds(Seconds) | Yes | Yes | Yes | Yes | |
| TimestampFromDateTime(Date,Time) | Yes | Yes | Yes | Yes | |
| TimestampFromSecondsSince(Seconds) | Yes | Yes | Yes | Yes | |
TimestampFromSecondsSince(Seconds,
Timestamp) |
Yes | Yes | Yes | Yes | |
| TimestampFromTimet(Time_t) | No | No | No | No | no SQL equivalent |
| TimestampToDate(Timestamp) | Yes | Yes | Yes | Yes | |
| TimestampToString(Timestamp) | Yes | Yes | Yes | Yes | |
| TimestampToString(Timestamp,Format) | Yes | Partial | Partial | Yes | For DB2, Format limited to "%YYYY-%MM-%DD %HH:%NN:%SS". For Netezza, Format does not include microseconds |
| TimestampToTime(Timestamp) | Yes | Yes | Yes | Yes | |
| TimetFromTimestamp(Timestamp) | No | No | No | No | no SQL equivalent |
| TimeToString(Time) | Yes | Yes | Yes | Yes | |
| TimeToString(Time,Format) | Yes | Partial | Yes | Yes | For DB2, Format limited to "%HH:%NN:%SS" |
| Trim(String) | No | No | No | No | No SQL equivalent for compressing internal white space |
| Trim(String,Option) | Partial | Partial | Partial | Partial | Option = 'E' or 'F' only. |
| Trim(String,StripChar,Option) | Partial | Partial | Partial | Partial | Option = 'B', 'L' or 'T' only |
| TrimB(String) | Yes | Yes | Yes | Yes | |
| TrimF(String) | Yes | Yes | Yes | Yes | |
| TrimLeadingTrailing(String) | Yes | Yes | Yes | Yes | |
| Upcase(String) | Yes | Yes | Yes | Yes | |
| UstringToString(UnicodeString) | No | No | No | No | no SQL equivalent |
UstringToString(UnicodeString,
MapName) |
No | No | No | No | no SQL equivalent |
| WeekdayFromDate(Date) | No | Yes | Yes | Yes | No Teradata SQL equivalent |
| YeardayFromDate(Date) | Yes | Yes | Yes | Yes | |
| YearFromDate(Date) | Yes | Yes | Yes | Yes | |
| YearweekFromDate(Date) | No | Yes | Yes | Yes | No Teradata SQL equivalent |