Toad Data Studio 1.1

Welcome to Toad Data Studio Understand Toad's Workflow

Create Amazon Redshift Connections Create ODBC Connections Create Oracle Connections Create Snowflake Connections Create SQL Server Connections Create SharePoint Connections Understand Toad Connections Troubleshoot SQL Server Connections

Understand

Determine the Best Method to Locate Objects Object Explorer Filter Databases and Objects Understand a Table Using the Database Explorer Search for Objects Create an Database Diagram to Use as a Query Template Browse Data in Related Tables

Query

Tutorial: Build a Query Visually Create Cross-Connection Queries Reverse Engineer Queries Build a Subquery Edit SQL Editor Shortcut Keys View Result Sets Compare Differences in Data Export Data with One Click Export Share Excel Linked Queries

Automate

Getting Started with Automation Tutorial: Automate Excel Reports Automate Tasks Use Database Automation Activities Use File Automation Activities Use System Automation Activities Use Variables In Automation Execute/Schedule Automation Scripts

Transformation Functions

ToChar Date and Numeric Functions

When using the ToChar function, you must use the .Net format strings. The following are some examples:

Format	Example
MM/dd/yyyy	08/26/2011
dddd, dd MMMM yyyy	Tuesday, 26 August 2011
dddd, dd MMMM yyyy h:mm tt	Tuesday, 26 August 2011 6:30 AM
MM/dd/yyyy h:mm tt	08/26/2011 6:30 AM
dddd, dd MMMM yyyy HH:mm:ss	Tuesday, 26 August 2011 06:30:07
MM/dd/yyyy HH:mm:ss	08/26/2011 06:30:07
####	1234
#.##	1.23
0.00	1.23
#,#	1,234,456
(###) ### - ####	(123) 456 - 7890

Notes:

Aggregate functions, such as Avg, cannot be used as parameters in transformation functions. To work around this, create a separate column containing output of the aggregate function and use this column as the parameter.
In functions, you must use a period (.) as a decimal separator. You cannot use a comma as a decimal separator.

Math Functions

Math functions return a numeric value based on input values supplied as arguments. Review the following for additional information:

Abs(number)	Returns the absolute value of a number.
Atan(number)	Returns the arc tangent of a number as a numeric value expressed in radians.
Ceiling(number)	Returns the first integer that is greater than or equal to the specified number.
Cos(number)	Returns a number in radians specifying the cosine of an angle.
Cosh(number)	Returns the hyperbolic cosine of a specified number. Notes: If a specified number is Null, the result of this function is Null. If a specified number is zero (0), the result of this function is one (1.0).
Count(field)	Returns the number of rows retrieved from the field. Note: This function counts only those records in which the field is not Null.
Exp(number)	Returns a number specifying e. The value of e is approximately 2.718282. Tip: This function is useful to see a relationship in which a percentage increase or decrease in the independent variable has the same proportional change in the dependent variable.
Floor(number)	Returns the first integer that is less than or equal to the specified number.
Hex(number)	Returns hexadecimal representation of the number.
Max(num1, num2)	Returns the maximum of two expressions.
Min (num1, num 2)	Returns the minimum of two expressions.
Power(number, n)	Calculates the number of times an expression is multiplied by itself.
Round(number)	Returns a similar value of a number while reducing the number of digits.
Sign(number)	Returns the sign of the number (positive values are 1, negative values are -1, and 0 is 0).
Sin(number)	Returns a number in radians specifying the sine of an angle.
Sinh(number)	Returns the hyperbolic sine of a number.
Sqrt(number)	Returns the square root of a number.
Tan(number)	Returns a number in radians specifying the tangent of an angle.
Tanh(number)	Returns the hyperbolic tangent of a number.
Trunc(number, precision)	Returns a number truncated to specified precision.

String Functions

You can use string functions to manipulate, change, or edit the contents of a string. Review the following for additional information:

Nvl(field,valueWhenNULL)	Returns a substitute value if a value is null where field represents the position of the first argument in succeeding arguments, and valueWhenNull represents the value to replace the null values.
If(expression, value, default)	Returns a default value for a row where expression evaluates to the default value, value represents the default value, and default assigns the default value.
Ascii(string)	Returns the numeric value of the first left character in a string where string represents the string to convert.
Concat(string1, string2)	Joins one or more strings to the current string and returns the result where string1 represents the first string to join and string2 represents the second string to join.
Initcap(string)	Changes the first letter of a string to uppercase where string represents the string to manipulate.
Instr(str1, str2, startPos,occurance)	Determines if one string contains another and returns the position of the first occurrence of one string within another where str1 represents the string to be searched, str2 represents the string searched for in str1, and startPos represents where the search is to begin in str1.
Length(string)	Returns the length of a string where string represents the string to manipulate.
Lower(string)	Returns the entire string in lowercase where string represents the string to manipulate.
Ltrim(str1,charsToRemove)	Removes the leftmost leading characters in a string where str1 represents the string to manipulate and charsToRemove represent characters in the trim list.
RemovePrefix(str1,str2)	Removes the leftmost leading characters in a string where str1 represents the string to manipulate and str2 represents the string to remove. If str2 is not found on the leading characters, str1 is returned. For example, RemovePrefix('IntMyVariable','Int') returns: MyVariable.
Replace(str1,str2,str3)	Replaces occurrences of a string where str1, str2, and str3 represent the strings to manipulate.
Rtrim(str1,charsToRemove)	Removes the rightmost trailing characters in a string where str1 represents the string to manipulate and charsToRemove represent characters in the trim list.
RemoveSuffix(str1,str2)	Removes the rightmost trailing characters in a string where str1 represents the string to manipulate and str2 represents the string to remove. If str2 is not found on the trailing characters, str1 is returned. For example, RemoveSuffix('MyVariableEnd','End') returns: MyVariable.
Substr(str1, start, length)	Returns the substring of a string where str1 represents the string to manipulate, start represents the beginning of the string to manipulate, and length represents the length of the string to manipulate.
Upper(String)	Returns the entire string in uppercase where String represents the string to manipulate.

Statistical Functions

You can use statistical functions to collect statistical information about column data. Review the following for additional information:

Median(number)	Calculates the middle value in a column of numbers arranged in value order where number represents the column of numbers. If a column contains an even number of rows, the mean of the two middle values are calculated.
Median(number, break)	Calculates the middle value in a column of numbers arranged in value order where number represents the column of numbers, and break represents an optional break column. If a column contains an even number of rows, the mean of the two middle values is calculated.
Mode(number)	Calculates the value that occurs most frequently in a column of numbers where numbers represents the column of numbers.
Mode(number, break)	Calculates the value that occurs most frequently in a column of numbers where numbers represents the column of numbers, and break represents an optional break column. Note: Mode includes values of zero (0) and ignores null values. If there are no duplicate values, the value of the first cell is returned.
Percentile(numbers, n)	Measures the nth percentile of the total frequency of values in a column of numbers where numbers represents the column of numbers, and n represents the percentile value zero to one.
Percentile(numbers, n, break)	Measures the nth percentile of the total frequency of values in a column of numbers where numbers represents the column of numbers, n represents the percentile value zero to one, and break represents an optional break column. Tip: You can also measure quartile values. Lower quartile - set nth value to .25% to remove lowest 25% of data. Interquartile - set the nth value to .5 for spread of the middle 50% of data. Upper quartile - set the nth value to .75 to remove the highest 25% of data.
Rank(numbers)	Calculates the sequence of numbers in a column in descending order where numbers represents the column of numbers.
Rank(numbers, break)	Calculates the sequence of numbers in a column in descending order where numbers represents the column of numbers, and break represents an optional break column. Note: Duplicate numbers have the same ranking and affect the ranking of subsequent numbers.
RankAsc(numbers)	Calculates the sequence of numbers in a column in ascending order where numbers represents the column of numbers.
RankAsc(numbers, break)	Calculates the sequence of numbers in a column in descending order where numbers represents the column of numbers, and break represents an optional break column. Note: Duplicate numbers have the same ranking and affect the ranking of subsequent numbers.
GeometricMean(number)	Returns an average calculated by multiplying all numbers in a set and taking the nth root of the total where number is the number of numbers.
GeometricMean(number, break)	Returns an average calculated by multiplying all numbers in a set and taking the nth root of the total where number is the number of numbers, and break represents an optional break column. Note: The geometric mean formula might produce large numbers.
StdDev(number)	Calculates the dispersion of values in a sampling of the data set from its average value (mean) where number represents the column of numbers. The more spread out the data, the higher the deviation. Tip: StdDev uses a sampling of the data to calculate standard deviation. Use StdDevp for the entire data set. Notes: An error is returned if the result set contains one row or less of data. The nonbiased or n-1 method is used to calculate standard deviation.
StdDev(number, break)	Calculates the dispersion of values in a sampling of the data set from its average value (mean) where numbers represents the column of numbers, and break represents an optional break column. Tip: StdDev uses a sampling of the data to calculate standard deviation. Use StdDevp for the entire data set. Notes: An error is returned if the result set contains one row or less of data. The nonbiased or n-1 method is used to calculate standard deviation.
StdDevp(number)	Calculates the dispersion of values in the entire data set from its average value (mean) where number represents the column of numbers. The more spread out the data, the higher the deviation. Tip: StdDevp uses the entire data set to calculate standard deviation. Use StdDev for a sampling of the data set. Note: The biased or n method is used to calculate standard deviation.
StdDevp(number, break)	Calculates the dispersion of values in the entire data set from its average value (mean) where number represents the column of numbers, and break represents an optional break column. The more spread out the data, the higher the deviation. Tip: StdDevp uses the entire data set to calculate standard deviation. Use StdDev for a sampling of the data set. Note: The biased or n method is used to calculate standard deviation.
Var(number)	Estimates variability drawn randomly from a sampling of data where number represents the column of numbers.
Var(number, break)	Estimates variability drawn randomly from a sampling of data where number represents the column of numbers, and break represents an optional break column. Tip: Var uses a sampling of the data to estimate variability. Use Varp for the entire data set.
Varp (number)	Estimates variability using the entire data set where number represents the column of numbers.
Varp(number, break)	Estimates variability using the entire data set where number represents the column of numbers, and break represents an optional break column. Tip: Varp uses the entire data set to estimate variability. Use Var for a sampling of the data set.

Trending Functions

A moving average is used to indicate a trend. It detects the start of a trend and follows its progress, including reporting its reversal. Review the following for additional information:

Simple Moving Averages	Calculate the mean of a window for every case in the data set. It sums all cases in the window and divides the result by the number of cases in the window. Each data value is given equal weight in the calculation. .
MovingAverage(number, window_size)	Where number represents the number of data points, and window_size represents a specific number of periods
MovingAverage(number, window_size, break_col)	Where number represents the number of data points, window_size represents a specific number of periods, and break_col represents an optional break column.
Weighted Moving Averages	Include multiplying factors to assign different weight to values from different periods. Usually, more recent a value, the higher it is weighted. To calculate a Weighted Moving Average Determine size of the calculation window, where n is the size of the window. Note: If the calculation window is said to be n, then the most recent data value in the window is multiplied by n, the next most recent multiplied by n-1, the value prior to that multiplied by n-2 and so on for all values in the Window. Divide the sum of the multiplied values by the sum of weights. Put Weighted Moving Average in a new a column.
MovingAverageWeighted(number, window_size)	Where number represents the number of data points, and window_size represents a specific number of periods.
MovingAverageWeighted(number, window_size, break_col)	Where number represents the number of data points, window_size represents a specific number of periods, and break_col represents an optional break column.
Exponential Moving Averages	Similar to Weighted Moving Averages, Exponential Moving Averages give more weight to recent data values, and all available data values are taken into account. Exponential Moving Averages are more accurate that other Moving Average calculations when original data values reveal rapid variability over time.
MovingAverageExponential (number, window_size)	Where number represents the number of data points, and window_size represents a specific number of periods.
MovingAverageExponential (number, window_size, break_col)	Where number represents the number of data points, window_size represents a specific number of periods, and break_col represents the column on which to perform the calculation.
Moving Diff	Moving Diff operates as a moving subtraction over a window and returns the difference between the current value and the first value in the window. Subtraction can only be performed on two numbers at a time. MovingDiff provides insight into a column’s recurring differences to alert the user to any abnormalities. This function does not calculate Weighted or Exponential averages.
MovingDiff(number, window_size)	Where number (required) represents the column containing the numeric value to subtract and window_size (optional) represents how many rows to use in the calculation.
MovingDiff(number, window_size, break_col)	Where number (required) represents the column containing the numeric value to subtract, window_size (optional) represents how many rows to use in the calculation, and break_col (optional) represents the column on which to perform the calculation. Notes: If using MovingDiff in the Pivot section, you must use a column already added to the Facts pane. Use a positive integer equal to or less than the total number of rows in a column for the window_size value. Window_size value defaults to three if no value is specified and to the number of rows in the column if the value is greater than the number of rows in the column.
Moving Maximum	Returns the maximum value contained in a moving window. This function does not calculate Weighted or Exponential averages.
MovingMax(number, window_size)	Where number (required) represents the column containing the numeric value to calculate and window_size (optional) represents how many rows to use in the calculation.
MovingMax(number, window_size, break_col	Where number (required) represents the column containing the numeric value to calculate, window_size (optional) represents how many rows to use in the calculation, and break_col (optional) represents the column on which to perform the calculation. Notes: If using MovingMax in the Pivot section, you must use a column already added to the Facts pane. Use a positive integer equal to or less than the total number of rows in a column for the window_size value. Window_size value defaults to three if no value is specified and to the number of rows in the column if the value is greater than the number of rows in the column.
Moving Median	MovingMed returns the middle value contained in a moving window. If the window’s value is an even number, use the mean of the two middle values. This function does not calculate Weighted or Exponential averages.
MovingMedian(number, window_size)	Where number (required) represents the column containing the numeric value to calculate and window_size (optional) represents how many rows to use in the calculation.
MovingMedian(number, window_size, break_col)	Where number (required) represents the column containing the numeric value to calculate, window_size (optional) represents how many rows to use in the calculation, and break_col (optional) represents the column on which to perform the calculation. Notes: If using MovingMed in the Pivot section, you must use a column already added to the Facts pane. Use a positive integer equal to or less than the total number of rows in a column for the window_size value. Window_size value defaults to three if no value is specified and to the number of rows in the column if the value is greater than the number of rows in the column.
Moving Minimum	Returns the minimum value contained in a moving window. This function does not calculate Weighted or Exponential averages.
MovingMin(number, window_size)	Where number (required) represents the column containing the numeric value to calculate and window_size (optional) represents how many rows to use in the calculation.
MovingMin(number, window_size, break_col)	Where number (required) represents the column containing the numeric value to calculate, window_size (optional) represents how many rows to use in the calculation, and break_col (optional) represents the column on which to perform the calculation. Notes: If using MovingMin in the Pivot section, you must use a column already added to the Facts pane. Use a positive integer equal to or less than the total number of rows in a column for the window_size value. Window_size value defaults to three if no value is specified and to the number of rows in the column if the value is greater than the number of rows in the column.
Moving Sum	Returns the sum of values contained in a moving window. This function does not calculate Weighted or Exponential averages.
MovingSum(number, window_size)	Where number (required) represents the column containing the numeric value to calculate and window_size (optional) represents how many rows to use in the calculation.
MovingSum(number, window_size, break_col)	Where number (required) represents the column containing the numeric value to calculate, window_size (optional) represents how many rows to use in the calculation, and break_col (optional) represents the column on which to perform the calculation. Notes: If using MovingSum in the Pivot section, you must use a column already added to the Facts pane. Use a positive integer equal to or less than the total number of rows in a column for the window_size value. Window_size value defaults to three if no value is specified and to the number of rows in the column if the value is greater than the number of rows in the column.

Please select your product:

To serve you better, please complete the Purpose of your Chat:

Recommended Solutions for Your Problem

Toad Data Studio 1.1 - User Guide