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 code

Select *
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 right-hand 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. int_start_position. The position from which to start extracting the substring. int_length. The number of characters to extract. 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>, left-padded 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>, right-padded 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 x-axis 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>, 'YYYY-MM-DD 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 non-null 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.

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 non-null 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: integer. The numbers of days to add. date. The date to add to. 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>, 'YYYY-MM-DD').
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 yyyy-MM-dd format is cast as datetime. In Oracle, to_date(<requested date in dd-MMM-yy format>, DD-MON-YY)

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 IF-THEN-ELSE 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 case-when-else 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.

Function

Signature

Return Type

Description

decode

decode(expression, search, result [, search, result]... [, default] )

expression

Returns the functionality of an IF-THEN-ELSE 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 case-when-else 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.

Work with DQL

Advantages of DQL

Additions to SQL

Supported functions

Send Help Center Feedback