Share this page
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.
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 defectWhere defect.detected_by = :me
-
- 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.
|
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.
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 string1 in string2, 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:
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. |
