Work with DQL
The process of creating business view queries utilizes DQL, a domain query language.
For the most part, DQL is an exact match to ANSI SQL 9.2, however there are some significant differences.
Note: DQL only supports SELECT statements.
Advantages of DQL
Building queries with DQL has the following advantages.
DQL enforces data hiding according to user's permission levels  Reports are generated according to the permission levels of the user generating them. Information that is included in a business view that is not available to a user does not appear in any report that the user creates. 
The DQL query creates a database abstraction, used as a basis for the business view  Because the basis of the view is an abstraction of the database and not the database itself, you do not need to identify entity fields according to their actual names. For example, all fields that relate to an object's identification appear with the suffix "ID", such as Defect ID, Cycle ID, and Release ID. 
DQL queries run equally well on Oracle and SQL database servers 
Additions to SQL
DQL provides the following options, which are not available in ANSI SQL:

Variables. Three variables have been added.
:me Returns information relevant to the user creating the report. Meaning different users who create reports based on the same business view will receive different results.
The :me variable looks like this:
Copy codeSelect *
From defect
Where defect.detected_by = :me:current_project_name Returns information relevant for the project from which the reports is created. :current_domain_name Returns information relevant for the domain from which the report is created.  Select Top. This option enables you to limit the query results to a defined number of items.
Supported functions
The following table lists the DQL functions that are supported in ALM. DQL functions are converted to the corresponding SQL functions for use in the database. The table lists the conversions of the DQL functions to MS SQL and Oracle. For details, refer to the MS SQL and Oracle documentation.
Aggregation
Function 
Signature 
Return Type 
Description 

count  count(expression)  integer  Returns the number of rows in a query. 
count_big  count_big(expression)  integer (bigint) 
Returns the number of rows in a query. The only difference between count and count_big is their return values. In MS SQL, count_big always returns a bigint data type value, and count always returns an int data type value. In Oracle, the function is converted to count. 
variance  variance(decimal)  decimal 
Returns the variance of the expression . In MS SQL, the function is converted to var. In Oracle, the function is coded as round(variance(<decimal>), 14). 
var_pop  var_pop(expression)  decimal 
Returns the population variance of a set of numbers after discarding the nulls in the set. In MS SQL, the function is converted to varp. In Oracle, the function is coded as round(var_pop(<expression>), 14). 
stddev  stddev(decimal)  decimal 
Returns the standard deviation of a set of numbers. In MS SQL, the function is converted to stdev. In Oracle, the function is coded as round(stddev(<decimal>), 14). 
stddev_pop  stddev_pop(expression)  decimal 
Computes the population standard deviation and returns the square root of the population variance. In MS SQL, the function is converted to stdevp. In Oracle, the function is coded as round(stddev_pop(<expression>), 14). 
avg  avg(expression)  decimal 
Returns the average value of an expression. In Oracle, the function is coded as round(avg(<decimal>), 14). 
sum  sum(decimal)  decimal  Returns the summed value of an expression. 
min  min(expression)  expression  Returns the minimum value of an expression. 
max  max(expression)  expression  Returns the maximum value of an expression. 
String Manipulation
Function 
Signature 
Return Type 
Description 

upper  upper(string)  string  Returns the given string with all letters converted to upper case. 
lower  lower(string)  string  Returns the given string with all letters converted to lower case. 
rtrim  rtrim(string)  string  Returns the given string with all trailing spaces removed from the righthand side. 
ltrim  ltrim(String)  string  Returns the given string with all leading spaces removed. 
replace  replace(string, string_to_replace, replacement_string)  string  Returns the given string with all sequences of <string_to_replace> replaced with <replacement_string>. 
substring  substring(string, int_start_position, int_length)  string 
Returns a substring from the given string.
In Oracle, the function is converted to substr. 
length  length(string)  decimal 
Returns the length of the specified string. In MS SQL, the function is converted to len. 
chr  chr(integer)  string 
Converts an int ASCII code into a character. In MS SQL, the function is converted to char. 
soundex  soundex(string)  string  Returns a phonetic representation of a string. 
ascii  ascii(string)  integer  Returns the ASCII code value of the leftmost character of a character expression. 
concat  concat(string1, string2)  string 
Concatenates two strings together . In MS SQL, the function is converted to +, and the function is coded as <string1> + <string2>. 
leftstr  leftstr(string, integer)  string 
Returns the left part of a character string with the specified number of characters. In MS SQL, the function is converted to left. In Oracle, the function is converted to substr and is coded as substr(<string>, 1, <integer>). 
lpad  lpad(string1, integer, string2)  string 
Returns <string1>, leftpadded to length <integer> characters with the sequence of characters in <string2>. This function is useful for formatting the output of a query. In MS SQL, the function is converted to the left padding expression, which implements the above logic. 
reverse  reverse(string)  string 
Returns the reverse of a string value. In Oracle, the function is coded as reverse(to_char(<string>)). 
rightstr  rightstr(string, integer)  string 
Returns the right part of a character string with the specified number of characters. In MS SQL, the function is converted to right. In Oracle, the function is converted to substr with the right part expression, which implements the above logic. 
rpad  rpad(string1, integer, string2)  string 
Returns <string1>, rightpadded to length <integer> characters with <string2>, replicated as many times as necessary. This function is useful for formatting the output of a query. In MS SQL, the function is converted to left with the right padding expression, which implements the above logic. 
stuff  stuff(string, integer, integer, string)  string 
Inserts a string into another string. It deletes a specified length of characters in the first string at the start position, and then inserts the second string into the first string at the start position. In Oracle, the stuff function is converted to an expression that implements the above logic. 
in_string  in_string(string1, string2, integer_start_location)  integer 
Returns the location of substring string1 in string2, if it exists. Otherwise, returns 0. The search starts at integer_start_location, which starts at 1. In MS SQL, the function is converted to charindex. In Oracle, the function is converted to instr and is coded as instr(<string2>, <string1>, <integer_start_location>). 
replicate  replicate(string, integer)  string 
Repeats the <string> value the number of times given in the <integer> parameter. In Oracle, the function is converted to rpad and is coded as rpad(<string>, length(<string>*<integer>), <string>). 
Math
Function 
Signature 
Return Type 
Description 

sin  sin(decimal)  decimal 
Returns the sine of the decimal parameter. In Oracle, the function is coded as round(sin(<decimal>), 14). 
asin  asin(decimal)  decimal 
Returns the arc sine of the decimal parameter. The argument must be in the range of 1 to 1. In Oracle, the function is coded as round(asin(<decimal>), 14). 
cos  cos(decimal)  decimal 
Returns the cosine of the decimal parameter. In Oracle, the function is coded as round(cos(<decimal>), 14). 
acos  acos(decimal)  decimal 
Returns the arc cosine of the decimal parameter. The argument must be in the range of 1 to 1. In Oracle, the function is coded as round(acos(<decimal>), 14). 
tan  tan(decimal)  decimal 
Returns the tangent of the decimal parameter. In Oracle, the function is coded as round(tan(<decimal>), 14). 
atan  atan(decimal)  decimal 
Returns the arc tangent of the decimal parameter. In Oracle, the function is coded as round(atan(<decimal>), 14). 
atan2  atan2(decimal1, decimal2)  decimal 
Returns the angle, in radians, between the positive xaxis and the ray from the origin to the point (y, x), where x and y are the values of decimal1 and decimal2. In MS SQL, the function is converted to atn2. In Oracle, the function is coded as round(atan2(<decimal1>), 14). 
tanh  tanh(decimal)  decimal 
Returns the hyperbolic tangent of the decimal parameter. In MS SQL, the function is converted to the tanh expression, which implements the above logic. In Oracle, the function is coded as round(tanh(<decimal>), 14). 
sqrt  sqrt(decimal)  decimal 
Returns the square root of decimal parameters. In Oracle, the function is coded as round(sqrt(<decimal>), 14). 
exp  exp(decimal)  decimal 
Returns e raised to the decimal parameter power, where e = 2.71828183. In Oracle, the function is coded as round(exp(<decimal>), 14). 
sign  sign(decimal)  decimal 
Returns a value indicating the sign of a number. If the number < 0, then sign returns 1. If the number = 0, then sign returns 0. If the number > 0, then sign returns 1. In MS SQL, the function is converted to the sign expression, which implements the above logic, and is coded as CAST(sign(<decimal>) AS int). 
floor  floor(decimal)  integer  Returns the largest integer less than or equal to the specified decimal argument. 
ln  ln(decimal)  decimal 
Returns the natural logarithm of the decimal argument. In MS SQL, the function is converted to log. In Oracle, the function is coded as round(ln(<decimal>), 14). 
abs  abs(decimal)  decimal  Returns the absolute value of a number. 
round  round(decimal, integer)  decimal 
Returns decimal rounded to integer places to the right of the decimal point. The integer parameter can be negative to round off digits left of the decimal point. In MS SQL, the function is coded as CAST(round(<decimal>, <integer>) AS int) or as CAST(round(<decimal>, <integer>) AS float), depending on whether integer is valued with zero. 
mod  mod(decimal1, decimal2)  decimal 
Returns the remainder of decimal1 divided by decimal2. In MS SQL, the function is converted to % and is coded as <decimal1> % <decimal2>. 
trunc_number  trunc_number(decimal, integer)  decimal 
Returns decimal truncated to integer decimal places. If integer is omitted, then decimal is truncated to 0 places. In MS SQL, the function is converted to the truncation expression, which implements the above logic. In Oracle, the function is converted to trunc. 
str  str(decimal, integer1, integer2)  string 
Returns character data converted from numeric data. In Oracle, the function is converted to the string construction expression, which implements the above logic. 
ceil  ceil(decimal)  decimal 
Returns the smallest integer greater than or equal to the decimal parameter. In MS SQL, the function is converted to ceiling. 
Cast
Function 
Signature 
Return Type 
Description 

to_number  to_number(string)  decimal 
Converts a string to a number. In MS SQL, the function is coded as CAST(<string> AS float). 
number_to_char  number_to_char(decimal)  string 
Converts a number to a string. In MS SQL, the function is coded as CAST(<decimal> AS varchar(50)). In Oracle, the function is converted to to_char. 
char_to_char  char_to_char(string)  string 
Converts NCHAR, NVARCHAR2, CLOB, or NCLOB data to the database character set. In MS SQL, the function is coded as CAST(<string> AS varchar(50)). In Oracle, the function is converted to to_char. 
datetime_to_char  datetime_to_char(expression)  string 
Converts a date or datetime to a string. In MS SQL, the function is coded as convert(varchar, <date>, 121). In Oracle, the function is converted to to_char and is coded as to_char(<date>, 'YYYYMMDD HH24:MI:SS:FF3'). 
Miscellaneous
Function 
Signature 
Return Type 
Description 

nullif  nullif(expression1, expression2)  expression  Compares expression1 and expression2. If expression1 and expression2 are equal, the function returns NULL. Otherwise, it returns expression1. 
coalesce  coalesce()  expression  Returns the first nonnull expression among its arguments. The data type of the returned value is the data type of the parameter with the highest data type precedence. 
isnull  isnull(check_expression, replace_expression)  expression 
Substitutes a value when a null value is encountered. If check_expression is null, then the function returns replace_expression. In Oracle, the function is converted to nvl. 
Date
Function 
Signature 
Return Type 
Description 

currentdate  currentdate()  date 
Returns the current date. In MS SQL, the function is coded as CAST(CAST(getdate() AS date) AS datetime). In Oracle, the function is coded as to_date(to_char(sysdate)). 
dateadd  dateadd(integer, date)  date 
Adds days to date parameters:
In MS SQL, the function is coded as dateadd(DAY, <int days>, <Date date>). In Oracle, the function is converted to + and is coded as <int days> + <Date date>. 
datediff  datediff(start_date, end_date)  integer 
Returns the count (signed integer) of dates crossed between the specified start_date and end_date. In MS SQL, the function is coded as datediff(DAY, <date>, <date>). In Oracle, the function is converted to  and is coded as trunc(<date>  <date>, 0). 
get_time  get_time()  datetime 
Returns the current date and time. In MS SQL, the function is converted to getdate and is coded as convert(datetime, getdate(), 126). In Oracle, the function is converted to LOCALTIMESTAMP. 
trunc_date  trunc_date(date, string_format)  date 
Returns a date truncated to a specific unit of measure. string_format is the unit of measure to apply for truncating. The following formats are supported: year, y, yy, yyyy, q, quarter, mm, month, d, dd, day, hh, mi In MS SQL, the function is converted to dateadd and is coded as dateadd(<unquoted string_format>, datediff(<unquoted string_format>, 0, <date>), 0). In Oracle, the function is converted to trunc and is coded as trunc(<date>, <string_format>. 
to_date  to_date(string)  date 
Converts a string to a date. In MS SQL, the function is coded as CAST(<string> AS datetime). In Oracle, the function is coded as to_date(<string>, 'YYYYMMDD'). 
timestamp_tz  timestamp_tz()  string 
Returns the current server time zone . In MS SQL, the function is converted to sysdatetimeoffset. In Oracle, the function is converted to CURRENT_TIMESTAMP. This function is not supported in MS SQL Server 2005. 
from_tz  from_tz(date, string)  date 
Converts a timestamp value (date)and a time zone (string)to a timestamp with time zone value. In MS SQL, the function is converted to the time stamp and zone expressions, which implement the above logic. This function is not supported in MS SQL Server 2005. 
sysdatetimeoffset  sysdatetimeoffset()  datetime with time zone 
Returns the system date, including fractional seconds, and the time zone of the system on which the database resides. In Oracle, the function is converted to SYSTIMESTAMP. This function is not supported in MS SQL Server 2005. 
getyear  getyear(date)  integer 
Returns an integer that represents the year of the specified date. In MS SQL, the function is converted to year. In Oracle, the function is converted to to_char and is coded as CAST(to_char(<date>, 'yyyy') AS number). 
getmonth  getmonth(date)  integer 
Returns an integer that represents the month of the specified date. In MS SQL, the function is converted to month. In Oracle, the function is converted to to_char and is coded as CAST(to_char(<date>, 'mm') AS number). 
getday  getday(date)  integer 
Returns an integer representing the day of the month of the specified date. In MS SQL, the function is converted to day. In Oracle, the function is converted to to_char and is coded as CAST(to_char(<date>, 'dd') AS number). 
datepart  datepart(string_datepart, date)  integer 
Returns an integer that represents the specified string_datepart of the specified date. In MS SQL, the function is coded as datepart(<unquoted string_datepart>, <date>). In Oracle, the function is converted to the datepart expression, which implements the above logic. 
datename  datename(string_datepart, date)  string 
Returns a character string that represents the specified string_datepart of the specified date. The following formats are supported: yyyy, yy, year, q, quarter, m, month, d, dd, day, hh, mi In MS SQL, the function is coded as datename(<unquoted string_datepart>, <date>). In Oracle, the function is converted to the datename expression, which implements the above logic. 
Special date
Note: For each function, the requested date is returned in the respective date format. In MS SQL, the requested date in yyyyMMdd format is cast as datetime. In Oracle, to_date(<requested date in ddMMMyy format>, DDMONYY)
Function 
Signature 
Return Type 
Description 

firstDayOfCurrentMonth  firstDayOfCurrentMonth()  date  Returns the date of the first day of the current month. 
firstDayOfCurrentWeek  firstDayOfCurrentWeek()  date  Returns the date of the first day of the current week. 
firstDayOfCurrentYear  firstDayOfCurrentYear()  date  Returns the date of the first day of the current year. 
firstDayOfNextMonth  firstDayOfNextMonth()  date  Returns the date of the first day of the next month. 
firstDayOfNextWeek  firstDayOfNextWeek()  date  Returns the date of the first day of the next week. 
firstDayOfNextYear  firstDayOfNextYear()  date  Returns the date of the first day of the next year. 
firstDayOfPreviousMonth  firstDayOfPreviousMonth()  date  Returns the date of the first day of the previous month. 
firstDayOfPreviousWeek  firstDayOfPreviousWeek()  date  Returns the date of the first day of the previous week. 
firstDayOfPreviousYear  firstDayOfPreviousYear()  date  Returns the date of the first day of the previous year. 
lastDayOfCurrentMonth  lastDayOfCurrentMonth()  date  Returns the date of the last day of the current month. 
lastDayOfCurrentWeek  lastDayOfCurrentWeek()  date  Returns the date of the last day of the current week. 
lastDayOfCurrentYear  lastDayOfCurrentYear()  date  Returns the date of the last day of the current year. 
lastDayOfNextMonth  lastDayOfNextMonth()  date  Returns the date of the last day of the next month. 
lastDayOfNextWeek  lastDayOfNextWeek()  date  Returns the date of the last day of the next week. 
lastDayOfNextYear  lastDayOfNextYear()  date  Returns the date of the last day of the next year. 
lastDayOfPreviousMonth  lastDayOfPreviousMonth()  date  Returns the date of the last day of the previous month. 
lastDayOfPreviousWeek  lastDayOfPreviousWeek()  date  Returns the date of the last day of the previous week. 
lastDayOfPreviousYear  lastDayOfPreviousYear()  date  Returns the date of the last day of the previous year. 
Case
Function 
Signature 
Return Type 
Description 

decode  decode(expression, search, result [, search, result]... [, default] )  expression 
Returns the functionality of an IFTHENELSE statement. Compares the expression to each search value one by one. If the expression is equal to a search, Oracle returns the corresponding result. If no match is found, Oracle returns default. If default is omitted, Oracle returns null. In MS SQL, the function is converted to the casewhenelse expression, which implements the above logic. 
greatest  greatest()  expression 
Returns the greatest of the list of parameters. In MS SQL, the function is converted to the greatest expression, which implements the above logic. 
least  least()  expression 
Returns the least of the list of parameters. In MS SQL, the function is converted to the least expression, which implements the above logic. 