Working 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 in accordance with user's permission levels. In other words, 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 which is then used as a basis for the business view. Since 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. Rather the query simplifies them and makes them easier to identify. 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.

Back to top

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.

    • :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.

    The Me variable looks like this:

    Select *
    From defect
    Where defect.detected_by = :me
  • Select Top. This option enables you to limit the query results to a defined number of items.

Back to top

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.

Function

Signature

Return Type

Description

Aggregation
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.

Note: In Oracle, the function is converted to count.

variance variance(decimal) decimal

Returns the variance of the expression .

Note: 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.

Note: 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.

Note: 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.

Note: 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.

Note: 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
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.

Note: In Oracle, the function is converted to substr.

length length(string) decimal

Returns the length of the specified string.

Note: In MS SQL, the function is converted to len.

chr chr(integer) string

Converts an int ASCII code into a character.

Note: 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 .

Note: 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.

Note: 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.

Note: 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.

Note: 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.

Note: 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.

Note: 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.

Note: 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 string2 in string1, if it exists. Otherwise, returns 0. The search starts at integer_start_location, which starts at 1.

Note: 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.

Note: In Oracle, the function is converted to rpad and is coded as rpad(<string>, length(<string>*<integer>), <string>).

Math
sin sin(decimal) decimal

Returns the sine of the decimal parameter.

Note: 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.

Note: In Oracle, the function is coded as round(asin(<decimal>), 14).

cos cos(decimal) decimal

Returns the cosine of the decimal parameter.

Note: 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.

Note: In Oracle, the function is coded as round(acos(<decimal>), 14).

tan tan(decimal) decimal

Returns the tangent of the decimal parameter.

Note: In Oracle, the function is coded as round(tan(<decimal>), 14).

atan atan(decimal) decimal

Returns the arc tangent of the decimal parameter.

Note: 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.

Note: 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.

Note: 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.

Note: 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.

Note: 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.

Note: 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.

Note: 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.

Note: 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.

Note: 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.

Note: 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.

Note: 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.

Note: In MS SQL, the function is converted to ceiling.

Cast
to_number to_number(string) decimal

Converts a string to a number.

Note: 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.

Note: 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.

Note: 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.

Note: 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
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.

Note: In Oracle, the function is converted to nvl.

Date
currentdate currentdate() date

Returns the current date.

Note: 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.

Note: 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.

Note: 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.

Note: 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

Note: 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.

Note: 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 .

Note: 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.

Note: 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.

Note: 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.

Note: 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.

Note: 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.

Note: 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.

Note: 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

Note: 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.

Case
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.

Note: 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.

Note: 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.

Note: In MS SQL, the function is converted to the least 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)

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.

Back to top