Chat now with support
Chat with Support

Toad Data Point 5.0 - User Guide

Using Intelligence Central Functions in Queries

When querying a View in Intelligence Central or when writing a cross-connection query, Toad allows you to use Intelligence Central functions instead of the usual database-specific functions. Toad then translates the function into the database specific syntax, if necessary.

This method can be convenient if you want to use the same query against two different database types. This method can also be useful when writing cross-connection queries.

Note: This feature is provided as an optional method. For best results when creating a cross-connection query, use the Query Builder to generate the correct syntax.

Where to Use Intelligence Central Functions

You can use the Intelligence Central functions in the following situations:

  • When querying a View in Intelligence Central—The View must be from one of the supported data sources listed below.
  • When writing a cross-connection query—Each data source in the cross-connection query must be one of the supported data sources listed below.

Supported Data Sources

You can use Intelligence Central functions in the situations described above against the following database types:

  • Oracle
  • SQL Server

Intelligence Central Functions

The following functions can be used in place of platform-specific functions in queries against the supported data sources listed in this topic.

Category Function Description
Date and Time CURDATE(), CURRENT_DATE, CURRENT_DATE() Returns the current date
  CURTIME(), CURRENT_TIME, CURRENT_TIME() Returns the current time
  NOW(), CURRENT_TIMESTAMP, CURRENT_TIMESTAMP() Returns the current date and time
  LOCALTIME(), LOCALTIME, LOCALTIMESTAMP(), LOCALTIMESTAMP Returns the current date and time
  DATE(expr) Extracts and returns the date from the expression
  TIME(expr) Extracts and returns the time from the expression
  TIMESTAMP(expr)

Returns the date (or datetime) expression as a datetime value

With two arguments, a time expression expr2 is added to a date or datetime expression expr1

  MAKEDATE(year,day-of-year) Returns the date created from the year and day-of-year arguments
  MAKETIME(hour,minute,second) Returns the time created from the hour, minute, and second arguments
 

ADDDATE(date,INTERVAL exp unit),

DATE_ADD(date,INTERVAL exp unit)

Adds a time value (interval) to a date value

Example:

ADDDATE('2014-06-01',INTERVAL 1 DAY)

returns '2014-06-02'

  DATE_FORMAT(date,format) Formats the date according to the format specified
  YEAR(date) Returns the year for date
  QUARTER(date) Returns the quarter of the year for date, in the range 1 to 4
  MONTH(date)

Returns the month for date

  HOUR(time) Extracts and returns the hour from time
  MINUTE(time) Extracts and returns the minutes from time
  SECOND(time) Extracts and returns the seconds from time
  DAYOFMONTH(date), DAY(date) Returns the day of the month for date
  DAYOFYEAR(date) Returns the day of the year for date, in the range 1 to 366
  TO_DAYS(date) Returns the number of days between date and year 0
  TO_SECONDS(expr) (Supported for Oracle only) If expr is a date or datetime, returns the number of seconds between expr and the year 0
  MICROSECOND(expr) Returns the microseconds from a date or datetime expression, in the range 0 to 999999
  MONTHNAME(date) Returns the name of the month for date
  DAYNAME(date) Returns the name of the weekday for date
 

SUBDATE((date,INTERVAL exp unit)),

DATE_SUB((date,INTERVAL exp unit))

Subtracts a time value (interval) from a date value
  ADDTIME(expr1,expr2) (Supported for SQL Server only) If expr1 is a time or datetime expression and expr2 is a time expression, adds the expressions and returns the result
  SUBTIME(expr1,expr2) (Supported for SQL Server only) If expr1 is a time or datetime expression and expr2 is a time expression, returns the result of expr1 - expr2
  TIMESTAMPADD(unit,interval,datetime_expr) Adds an interval to a date or datetime expression
  TIMESTAMPDIFF(unit,expr1,expr2)

(Supported for SQL Server only) Returns the result of expr2 - expr1 where the expressions are date or datetime expressions. The unit for the result (an integer) is determined by unit.

Example:

TIMESTAMPDIFF(MONTH,'2014-01-01','2014-06-01')

returns 5

String CONCAT(str1,str2,...) Returns the string result from concatenating the arguments
  LENGTH(str) Returns the length of the string, in bytes
  LOCATE(substr,str) Returns the position of the first occurrence of the sub-string substr in the string str

Example:

LOCATE('cde', 'abcdef')

returns 3

  LOCATE(substr,str,pos)

Returns the position of the first occurrence of the sub-string after the position specified by pos

Example:

LOCATE('cde','abcdefabcdef',6)

returns 9

 

SUBSTR(str,pos)

SUBSTRING(str,pos)

Returns a sub-string from the string str starting at the position specified by pos
 

SUBSTR(str,pos,len)

SUBSTRING(str,pos,len)

Returns a sub-string from the string str starting at the position specified by pos and containing the number of characters specified by len
  LEFT(str,len)

Returns a string containing the number of characters specified by len from the left side of the string str

  RIGHT(str,len) Returns a string containing the number of characters specified by len from the right side of the string str
  REPLACE(str,from_str,to_str) Returns the string str with each instance of the string from_str replaced by the string to_str
  UPPER(str) Returns the string str with all characters in uppercase
  LOWER(str) Returns the string str with all characters in lowercase
  TRIM(str)

Returns the string with leading and trailing spaces removed

  LTRIM(str) Returns the string with leading spaces removed
  RTRIM(str) Returns the string with trailing spaces removed
Flow Control IFNULL(expr1,expr2)

If expr1 is NULL, this function returns expr2.

If expr1 is not NULL, this function returns expr1.

Select statement clauses GROUP BY ordinal Group by the column in the position specified by ordinal
  Limit count Limits the number of rows returned to the number specified by count

  

Related Documents