Reference Information: Query Joins, Functions, and Hyperlinks

About APM Inner Joins

When you use two related entity families as query sources, APM creates an inner join between the two entity families by default. This inner join causes the query results to include only the records that are linked through the specified relationship.

If two entity families are not related through a relationship family, you can manually create an inner join to connect one or more fields in one family to one or more fields in the other family.

The following table shows the line style that appears in the design canvas to represent APM inner joins.


Join Type	Line Style
APM Inner Join

Example: Inner Join

Suppose you add the Equipment entity family, Work History entity family, and Has Work History relationship family as query sources. Assuming that the Has Work History family relates the Equipment family to the Work History family, APM will create an inner join between the Equipment and Work History families by default. As a result, the query results will return only Equipment records that are linked to a Work History record through the Has Work History relationship. The query results will not include any Equipment records that are not linked to a Work History record or any Work History records that are not linked to an Equipment record.

About APM Outer Joins

An outer join allows you to return records that satisfy the join conditions and records from one family for which there are no matching records in the other family.

You can modify the default inner join to manually create an outer join for families that are related through a relationship family.

The following table shows the line styles that appear in the design canvas to represent APM outer joins.


Join Type	Line Style
APM Outer Left Join
APM Outer Right Join

Example 1: Creating a APM Outer Join

If you use two unrelated families as query sources, such as the Centrifugal Pump family and the Rotary Pump family, you can create an outer join to view all Centrifugal Pumps and Rotating Pumps whose manufacturer is the same and all Centrifugal Pumps with a different manufacturer. If the Centrifugal Pump family was added as a query source as Table #1 and the Rotary Pump family was added as Table #2, this would create a left join, which would be indicated in the SQL code.

On the other hand, if you wanted to view all Rotary Pumps and Centrifugal Pumps whose manufacturer is the same and all Rotary Pumps with a different manufacturer, you would create a right join, which would be indicated in the SQL code.

Example 2: Creating a APM Outer Join

You can modify a relationship-driven inner join to create an outer join. For example, suppose that you use the Centrifugal Pump entity family, the Failure entity family, and the Asset Has Failure relationship family as query sources. If your system is configured such that the Asset Has Failure family relates the Centrifugal Pump family to the Failure family, APM would create an inner join between the two entity families automatically.

If you ran the query using the default join, the results would include all Centrifugal Pump records that are linked to a Failure record. If you wanted to modify those results, however, to view all Centrifugal Pumps with their linked Failure records and all Centrifugal Pumps that did not have linked Failure records, you would need to modify the inner join to create an outer join. This would return Centrifugal Pumps with and without linked Failure records, but not Failure records without linked Centrifugal Pump records. For example, you could configure a query to show all the Centrifugal Pump records, where only three are linked to Failure records.

This type of join is considered a left join, as indicated by the following SQL code:


                    SELECT [Centrifugal Pump].[ASSET_ID_CHR] "Asset ID", [Failure].[EFAIL_ASSETID_CHR] "Failure ID"
                    
                        FROM [Centrifugal Pump]RIGHT JOIN SUCC [Failure] ON {Asset Has Failure}

You could also decide to return all Failure records with their linked Centrifugal Pump records and all Failure records that do not have linked Centrifugal Pump records.

This type of join is considered a right join, as indicated by the following SQL code:


                    SELECT [Centrifugal Pump].[ASSET_ID_CHR] "Asset ID", [Failure].[EFAIL_ASSETID_CHR] "Failure ID"
                    
                        FROM [Centrifugal Pump]RIGHT JOIN SUCC [Failure] ON {Asset Has Failure}

About Manual Joins

In many cases, you will want to query the database for information that exists in more than one family. When you do so, you may also want to join a field in a family with a similar field in another family, which will create an ad hoc association between the two families. By joining a field in one family to a field in another family, you create a query join.

Details

There are two types of manual joins in APM:

Manual Inner Join: Returns a row for every record where the values in the joined fields in both families are equal. An inner join returns only those records that satisfy the join conditions, so any unmatched records are dropped from the result set.
Manual Outer Join: Returns a row for every record in one family, and a row for every record where the values in the joined fields in both families are equal. An outer join can be either a left outer join or a right outer join. When you add query sources to the grid in the Conditions section and join their fields, the family that you added first is referred to as Table #1, and the family that you added second is referred to as Table #2.
- A left outer join returns every record in Table #1, regardless of whether each record is linked to a record from Table #2. The results also include the records in Table #2 in which the value in the joined field is equal to that in Table #1.
- A right outer join returns every record in Table #2, regardless of whether each record is linked to a record in Table #1. The results also include the records in Table #1 in which the value in the joined field is equal to that in Table #2.

The following table shows the line styles that appear in the design canvas to represent manual joins.


Join Type	Line Style
Manual Inner Join
Manual Outer Left Join
Manual Outer Right Join

Example: Manual Inner Join

Suppose that you notice that two pieces of equipment, such as a centrifugal pump and an air cooled heat exchanger, have been malfunctioning recently. You suspect that the failures could be caused by human error during installation. You decide to query the database to find instances where these pieces of equipment were installed on the same day by the same person.

To do so, you would need to add the Centrifugal Pump family and the Air Cooled Heat Exchanger family as query sources. You would then need to join the Asset Installation Date fields and the Responsible Installer fields between the two families via an inner join. Doing so would return only records where a centrifugal pump was installed by the same person on the same date as an air cooled heat exchanger.

What is a Function?

A function is a SQL component that manipulates data and returns a value that is not stored in the database, but is derived from calculating or reformatting values. Functions can be used to calculate or reformat values:

Stored in the database.
Based on static data (e.g., the current date).

In SQL code, a function can be included as part of the SELECT statement, WHERE clause, the GROUP BY clause, or the HAVING clause, or it can exist outside of these SQL components. You can write functions in the SQL code, or in the Expression Builder.

You can use the following functions in APM:

Tip: For more information on functions, refer to MetaSQL Functions.

Note: Throughout this documentation, functions are grouped into categories according to how they are grouped in the Expression Builder.

Example 1: SUM Function Contained within the SELECT Statement

In the following SQL code, the SUM function is displayed in bold text.

SELECT [Asset].[ASSET_ID_CHR] "Asset ID", Sum([Failure].[EFAIL_TOTCST_FROM]) "Total Failure Cost"
 FROM [Asset] JOIN SUCC [Failure] ON {Asset Has Failure}
 WHERE [Failure].[EFAIL TOTCST FRM] > 50000
GROUP BY [Asset].[ASSET ID CHR]

In this example, the SUM function is contained within the SELECT statement.

Example 2: SUM Function Contained within the SELECT Statement and in the HAVING Clause

In the following SQL code, the SUM function is displayed in bold text and appears twice: once in the SELECT statement and once in the HAVING clause.

SELECT [Asset].[ASSET_ID_CHR] "Asset ID", Sum([Failure].[EFAIL_TOTCST_FROM]) "Total Failure Cost"
FROM [Asset] JOIN SUCC [Failure] ON {Asset Has Failure}
GROUP BY [Asset].[ASSET ID CHR]
HAVING Sum([Failure].[EFAIL TOTCST FROM]) > 5000

About the GROUP BY Clause

A GROUP BY clause is used to group query results.

The GROUP BY Clause

Suppose that you want to see the total number of failures your equipment has experienced per equipment manufacturer.

You would add the Asset Manufacturer field and the Failure ID field to the grid in the Conditions section. The Total row indicates that you want to group the results by manufacturer and display a total count of failures for each manufacturer that is returned. The sort preference indicates that you want to sort the results in descending order according to the failure count.

In the results, each row represents a different manufacturer and the total count of failures for each manufacturer that is returned.

In this example, the query results are grouped by one field only, so each manufacturer appears only one time. You can, however, group query results by more than one field. When you group a query by multiple rows, the query determines all possible combinations of results and returns each distinct combination. Therefore, the more fields you group by, the more results you will see.

Continuing with this example, if you add the Asset ID field to the query, the results will contain many more rows because there are more combinations to display. In this case, one manufacturer may be displayed twice if that manufacturer manufactures multiple equipment items.

About Aggregate and Analytic Functions

Aggregate functions perform a calculation on a set of values and return a single value. Analytic functions compute an aggregate value based on a set of values, and, unlike aggregate functions, can return multiple rows for each set of values. Throughout this documentation, we refer to queries that contain aggregate functions as aggregate queries, and queries that contain analytic functions as analytic queries.

If you want to run a query using only aggregate functions, the query will return one row with a column for each field. If you want to run a query using an aggregate function in conjunction with the GROUP BY clause, the query will return one row for each value found in the grouped field.

Aggregate functions are used in conjunction with the GROUP BY clause, which specifies how the query results will be grouped and displayed. In other words, if you use any aggregate functions on fields in a query, then all remaining fields must appear in the GROUP BY clause.

Aggregate functions and analytic functions can be selected in the Total cell of the grid in the Conditions section.

The SUM and AVERAGE functions can be used only on numeric query fields. The MIN, MAX, and COUNT functions can be used on date, numeric, or character fields.

Tip: For more information on functions, refer to MetaSQL Functions.

Analytic functions compute an aggregate value based on a set of values, and, unlike aggregate functions, can return multiple rows for each set of values. Use analytic functions to compute moving averages, running totals, percentages, or top-N results within a group.

If you want to run a query using an analytic function, the query will return one row for each field in the defined range of fields used to perform the calculations.

Note: In the following table, items in the special font (that is, field, field1) represent user-supplied parameters. Items contained within brackets are optional. Do not enter the brackets themselves.

Analytic Function	Format of Code Using the Function
AVG	AVG(`field`) OVER([PARTITION BY `field1`[, `field2`, ...]] ORDER BY `field1`[, `field2`, ...])
CUME_DIST	CUME_DIST() OVER([PARTITION BY `field1`[, `field2`, ...]] ORDER BY`field1`[, `field2`, ...])
FIRST_VALUE	FIRST_VALUE(`field`) OVER([PARTITION BY `field1`[,`field2`, ...]] ORDER BY `field1`[, `field2`, ...])
LAST_VALUE	LAST_VALUE(`field`) OVER([PARTITION BY `field1`[,`field2`, ...]] ORDER BY `field1`[, `field2`, ...])
MAX	MAX(`field`) OVER([PARTITION BY `field1`[,`field2`, ...]] ORDER BY `field1`[, `field2`, ...])
MIN	MIN(`field`) OVER([PARTITION BY `field1`[, `field2`, ...]] ORDER BY `field1`[, `field2`, ...])
NTILE	NTILE(`number`) OVER([PARTITION BY `field1`[,`field2`, ...]] ORDER BY `field1`[, `field2`, ...])
ROW	ROW_NUMBER() OVER([PARTITION BY`field1`[, `field2`, ...]] ORDER BY `field1`[, `field2`, ...])
SUM	SUM(`field`) OVER([PARTITION BY `field1`[,`field2`, ...]] ORDER BY `field1`[,`field2`, ...])

About Character Functions

Character functions are used to manipulate values returned on character fields.

Tip: For more information on functions, refer to MetaSQL Functions.

Example: REPLACE Function

Suppose that you want to view the Failure ID of the failure associated with each piece of equipment or location in your database. Failure IDs are stored with a dash in the syntax, and you want to display them with a double colon instead of the dash.

In this case, you might configure a query on the Asset and Failure families, joined via the Asset Has Failure relationship, and add the Asset ID and Failure ID fields to the query.

In this example, to return the Failure IDs and replace the dash with a double colon, you would configure an expression using the REPLACE function. You would also configure the alias to ensure that the column displays the text Failure ID.

In this case, the expression syntax is:


                    REPLACE([Failure].[Failure ID], '-', '::')

The syntax indicates that you want to replace the dash (-) with a double colon (::).

More Examples of Character Functions

The following table lists more examples of using Character functions:


Function	Description	Example	Stored Value	Result
CONCAT	Concatenates two field values and displays the result.	CONCAT ([Asset)ID], [Asset Type])	Asset ID: T-101 Asset Type: Tank	T-101Tank
&	Concatenates two or more field values and adds delimiters between the values.	[Asset ID] &':' & [Asset Type]	Asset ID: T-101 Asset Type: Tank	T-101:Tank
LOWER	Displays the value in all lowercase letters.	LOWER ([Asset Type])	Tank	Tank
UPPER	Displays the value in all uppercase letters.	UPPER ([Asset Type])	Tank	Tank
SUBSTR	Displays a specific number of characters depending on the starting point you specify and the number of characters that you specify should be returned.	SUBSTR ([Asset ID],0,3) Zero (0) specifies the starting point (from left to right) and three (3) specifies the number of characters after the starting point that you want to display.	PMP-101	PMP
IsNull	Displays a specified value when a null value is found.	IsNull ([Asset Type],'No Value Listed')	Asset Type field contains a null value	No Value Listed
LTRIM	Displays the value with the specified characters removed from the beginning (left) of the string.	LTRIM ([PhoneNumber], '(540)-')	(540) 344-9205	344-9205
RTRIM	Displays the value with the specified characters removed from the end (right) of the string.	RTRIM ([Failure ID], '-0123456789')	FAIL-1234	FAIL
STRING_AGG	Concatenates the string values and places separator values between them. The separator is not added at the end of string. If this function is used with group by, it returns one row per grouping, else returns one row. You can also control the order in which the values are concatenated. The SQL server has a 8,000 character limit. Oracle and Postgres return CLOB and TEXT respectively.	SELECT String_Agg([MI_EQUIP000].[MI_EQUIP000_EQUIP_ID_C], ',') "Equipment" FROM [MI_FNCLOC00] JOIN_SUCC [MI_EQUIP000] ON {MIR_FLHSEQ} GROUP BY [MI_FNCLOC00].[MI_FNCLOC00_FNC_LOC_C]	000000000001056781 000000000010000068	000000000001056781,000000000010000068
JSON_VALUE	Gets a single value from a JSON string. Always returns as a character.	JSON_VALUE([FAMILY].[JSON field], `$.property')	{"property": 1}	"1"

About Conversion Functions

A Conversion function is used to modify query results by reformatting the data. You can use this type of function if you want to reformat the data to simplify it (e.g., you could remove unnecessary zeros from dates, where 01/07/2007 could be converted to 1/7/2007) or if you want to reformat the data so it appears as a different data type completely (e.g., you could spell out a month instead of representing the month with a number, where 01/07/2007 could be converted to January 7, 2007).


Description	Function
Convert a number or date to a character	CastChar
Convert a character to a date	CastDate
Convert a character to a number	CastNum

About Date Functions

Date functions are used to manipulate values returned on date fields.

Tip: For more information on functions, refer to MetaSQL Functions.

Example: MI_DateAdd

Suppose that all air cooled heat exchangers in your facility require inspection every six months. You might want to run a query to see the last inspection date of all air cooled heat exchangers and the date that is six months after that date. If each Air Cooled Heat Exchanger record in your database contains an Asset Inspection Date value, you could do so using the MI DateAdd function.

Note: Oracle databases do not support date math by year ('yy') or month ('mm') with time stamps enabled; therefore, the DateAdd function for Oracle databases will use 365.25 days for years and 30 days for months.

You might configure a query on the Air Cooled Heat Exchanger family, and add the Asset ID, Asset Description, Asset Inspection Date, and Next Inspection Date fields to the query.

In this example, to return the next inspection date, you would configure an expression using the MI DateAdd function. You would also configure the alias of this column to indicate that the column returns the next inspection date.

The expression syntax is:

MI DateAdd('mm', 6, [Air Cooled Heat Exchanger].[Asset Inspection Date])

The following table lists more examples of how you can use the MI DateAdd function.


Parameter	Example	Description	Stored Value	Result
yy	MI DateAdd(' yy ', 1, [Air Cooled Heat Exchanger].[Asset Installation Date])	Adds one year to the date on the Asset Installation Date field in Air Cooled Heat Exchanger records.	11/01/2009	11/01/2010
mm	MI DateAdd(' mm ', 6, [Air Cooled Heat Exchanger].[Asset Installation Date])	Add six months to the date on the Asset Installation Date field in Air Cooled Heat Exchanger records.	11/01/2009	05/01/2010
dd	MI DateAdd(' dd ', 4, [Air Cooled Heat Exchanger].[Asset Installation Date])	Adds four days to the date on the Asset Installation Date field in Air Cooled Heat Exchanger records.	11/01/2009	11/05/2009
hh	MI DateAdd(' hh ', 4, [Air Cooled Heat Exchanger].[Asset Installation Date])	Adds four hours to the date on the Asset Installation Date field in Air Cooled Heat Exchanger records.	11/01/2009 4:00:00	11/01/2009 8:00:00
mi	MI DateAdd(' mi ', 4, [Air Cooled Heat Exchanger].[Asset Installation Date])	Adds four minutes to the date on the Asset Installation Date field in Air Cooled Heat Exchanger records.	11/01/2009 4:00:00	11/01/2009 4:04:00
mi	MI DateAdd(' mi ', -4, [Air Cooled Heat Exchanger].[Asset Installation Date])	Subtracts four minutes from the date on the Asset Installation Date field in Air Cooled Heat Exchanger records.	11/01/2009 4:00:00	11/01/2009 3:56:00
ss	MI DateAdd(' ss ', 4, [Air Cooled Heat Exchanger].[Asset Installation Date])	Adds four seconds to the date on the Asset Installation Date field in Air Cooled Heat Exchanger records.	11/01/2009 4:00:00	11/01/2009 4:00:04

Example: MI_DatePart

Suppose that you want to view only the month in which the shell and tube heat exchangers were installed in your facility. If all Shell and Tube Heat Exchanger records in your database contain an Asset Installation Date field, and the values are stored in the format mm/dd/yyyy, you could do so using the MI DatePart function.

You might configure a query on the Shell and Tube Heat Exchanger family, and add the Asset ID, Asset Description, and Installation Month fields to the query.

In this example, to return the month in which each shell and tube heat exchanger was installed, you would configure an expression using the MI DatePart function. You would also configure the alias to indicate that the column returns the year the piece of equipment was installed.

The expression syntax is:

MI DatePart('mm', [Shell and Tube Heat Exchanger].[Asset Installation Date])

Using this syntax, instead of displaying the installation date in the stored format of mm/dd/yyyy, the query results will display only the value representing the month.

The following table lists more examples of how you can use the MI DatePart function.


Parameter	Example	Description	Stored Value	Result
yy	MI DatePart('yy', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays only the year from a stored date value.	11/01/2009	2009
yyyy	MI DatePart('yyyy', [Shell and Tube Heat Exchanger].[Asset Installation Date])
year	MI DatePart('year', [Shell and Tube Heat Exchanger].[Asset Installation Date])
q	MI DatePart('q', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays a number representing the quarter of the year based on a stored date value.	11/01/2009	4
qq	MI DatePart('qq', [Shell and Tube Heat Exchanger].[Asset Installation Date])
quarter	MI DatePart('quarter', [Shell and Tube Heat Exchanger].[Asset Installation Date])
m	MI DatePart('m', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays only the month from a stored date value.	11/01/2009	11
mm	MI DatePart('mm', [Shell and Tube Heat Exchanger].[Asset Installation Date])
month	MI DatePart('month', [Shell and Tube Heat Exchanger].[Asset Installation Date])
wk	MI DatePart('wk', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays a number representing the week of the month based on a stored date value.	11/01/2009	1
weekofmonth	MI DatePart('weekofmonth', [Shell and Tube Heat Exchanger].[Asset Installation Date])		11/01/2009	1
d	MI DatePart('d', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays only the day from a stored date value.	11/01/2009	01
dd	MI DatePart('dd', [Shell and Tube Heat Exchanger].[Asset Installation Date])
day	MI DatePart('day', [Shell and Tube Heat Exchanger].[Asset Installation Date])
dw	MI DatePart('dw', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays a number representing the day of the week based on a stored date value (where 1 = Sunday, 2 = Monday, etc.).	11/01/2009	1
weekday	MI DatePart('weekday', [Shell and Tube Heat Exchanger].[Asset Installation Date])		11/01/2009	1
y	MI DatePart('y', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays a number representing the day of the year based on a stored date value.	11/01/2009	305
dy	MI DatePart('dy', [Shell and Tube Heat Exchanger].[Asset Installation Date])
dayofyear	MI DatePart('dayofyear', [Shell and Tube Heat Exchanger].[Asset Installation Date])
hh	MI DatePart('hh', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays only the hour from a stored date value.	11/01/2009 4:00:00	4
hour	MI DatePart('hour', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays only the hour from a stored date value.	11/01/2009 4:00:00	4
n	MI DatePart('n', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays only the minutes from a stored date value.	11/01/2009 4:00:00	00
mi	MI DatePart('mi', [Shell and Tube Heat Exchanger].[Asset Installation Date])
minute	MI DatePart('minute', [Shell and Tube Heat Exchanger].[Asset Installation Date])
s	MI DatePart('s', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays only the seconds from a stored date value.	11/01/2009 4:00:00	00
ss	MI DatePart('ss', [Shell and Tube Heat Exchanger].[Asset Installation Date])
second	MI DatePart('second', [Shell and Tube Heat Exchanger].[Asset Installation Date])

Example: NOW

Suppose that you want to see the tasks that are assigned to a specific user and due in the next thirty days. If Task records contain the Next Date field that stores the date on which the task is due, you could do so using the NOW function.

You might configure a query on the Task family, and add the Task ID, Next Date, and Task Assigned To fields to the query.

In this example, you would configure an expression on the Next Date field using the NOW function. Additionally, you would configure a prompt to prompt the user for the name of the user whose Task records you want to view.

The expression syntax is:

(>= Now() AND <= (Now() + 30))

The syntax indicates that you want to view the tasks that are due today and within the next thirty days. For example, if a task is due thirty-one days after today's date, it will not appear in the query results.

The following table lists more examples of how you can use the NOW function.


Example Expression	Description	Current Date	Result
NOW(0) or NOW()	Displays the current date.	11/01/2009	11/01/2009
NOW() - 3	Displays the date that is three days prior to the current date.	11/01/2009	10/29/2009
NOW() + 3	Displays the date that is three days later than the current date.	11/01/2009	11/04/2009

Example: LastDate

Suppose that you want to see all the air cooled heat exchanger failures in your facility that occurred in a given month. If each Air Cooled Heat Exchanger record is linked to a Failure record, and each Failure record contains the Failure Date/Time field that stores that date and time on which a failure occurred, you could do so using the LAST DATE function.

You might configure a query on the Air Cooled Heat Exchanger and Failure families, joined via the Asset Has Failure relationship, and add the Asset ID, Failure ID, Failure Date/Time, and Last Day fields to the query.

In this example, you would configure an expression using the LAST DATE function. Additionally, you would create a prompt that prompts you to select the last day of the month whose failures you want to view. You would configure the alias to indicate that the column displays the last day of the month.

The expression syntax is:

LastDate([Failure].[Failure Date/Time])

The prompt syntax is:

((? :d :caption='Last Day' :id=Last Day))

When you run the query, a prompt appears, where you can select 11/30/2009 12:00:00 A.M. to indicate that you want to view failures that occurred during the month of November.

Example: ISNULL

Suppose that you want to see the installation date of the shell and tube heat exchangers in your facility even if the Asset Installation Date field in the Shell and Tube Heat Exchanger records is empty, and if the Asset Installation Date field is empty, you want to show the value Not Installed.

In this case, you might configure a query on the Shell and Tube Heat Exchanger family, and add the Asset ID, Asset Description, and Installation Dates (ALL) fields to the query.

In this example, you would configure an expression using the ISNULL function. You would also configure the alias to indicate that the column returns the installation dates.

In this case, the expression syntax is:

IsNull([Shell and Tube Heat Exchanger].[Asset Installation Date], 'Not Installed')

...where Not Installed is the value that will appear where a null value is found on the Asset Installation Date field in the Shell and Tube Heat Exchanger records.

Example: ROUND

Suppose that you want to see the values on the As Left fields for the analyzer instruments in your facility rounded to two decimal places. If all Analyzer Calibration records contain the As Left field, you can do so using the ROUND function.

You might configure a query on the Analyzer Calibration family, and add the Analyzer ID, Calibration Date, and As Left (rounded) fields to the query.

In this example, you would configure an expression using the ROUND function. You would also configure the alias to indicate that the column displays the rounded as left values.

The expression syntax is:

ROUND([Analyzer Calibration].[As Left], '2')

...where 2 is the number of decimal places to which the values will be rounded in the results.

Example: DATENAME

Suppose that you want to see the name of the month in which the shell and tube heat exchangers were installed. Instead of 11, you want to see November, and you do not want to return the year or day in the query results. If all Shell and Tube Heat Exchanger records in your database contain an Asset Installation Date field, and the values are stored in the format mm/dd/yyyy (e.g., 11/01/2009), you could do so using the DATENAME function.

You might configure a query on the Shell and Tube Heat Exchanger family, and add the Asset ID, Asset Description, and Installed by Month fields to the query.

In this example, you would configure an expression using the DATENAME function. You would also configure the alias to indicate that the results display the month.

The expression syntax is:

DATENAME('mm', [Shell and Tube Heat Exchanger].[Asset Installation Date])

...where mm indicates the part of the date (i.e., month) that you want to convert to its character format.

The following table lists more examples of how you can use the DATENAME function.


Parameter	Description	Example	Stored Value	Result
yy year	Displays a number representing the year from a stored date value.	DATENAME('year', [Shell and Tube Heat Exchanger].[Asset Installation Date])	11/01/2009	2009
qq quarter	Displays a number representing the quarter of the year based on a stored date value.	DATENAME('qq', [Shell and Tube Heat Exchanger].[Asset Installation Date])	11/01/2009	4
mm month	Displays the month from a stored date value.	DATENAME('mm', [Shell and Tube Heat Exchanger].[Asset Installation Date])	11/01/2009	November
ww	Displays a number representing the week of the month based on a stored date value.	DATENAME('ww', [Shell and Tube Heat Exchanger].[Asset Installation Date])	11/01/2009	1
dd day	Displays a number representing the day from a stored date value.	DATENAME('day', [Shell and Tube Heat Exchanger].[Asset Installation Date])	11/01/2009	01
dw	Displays the day of the week based on a stored date value.	DATENAME('dw', [Shell and Tube Heat Exchanger].[Asset Installation Date])	11/01/2009	Sunday
dy dayofyear	Displays a number representing the day of the year based on a stored date value.	DATENAME('dayofyear', [Shell and Tube Heat Exchanger].[Asset Installation Date])	11/01/2009	305

Example: DATEPART

Suppose that you want to see the year in which the shell and tube heat exchangers were installed in your facility. If all Shell and Tube Heat Exchanger records in your database contain an Asset Installation Date field, and the values are stored in the format mm/dd/yyyy (e.g., 11/01/2009), you could do so using the DATEPART function.

You might configure a query on the Shell and Tube Heat Exchanger family, and add the Asset ID, Asset Description, and Installation Year fields to the query.

In this example, you would configure an expression using the DATEPART function. You would also configure the alias to indicate that the column displays the installation year.

The expression syntax is:

DATEPART('year', [Shell and Tube Heat Exchanger].[Asset Installation Date])

...where year is the portion of the date value that you want to view.


Parameter	Example	Description	Stored Value	Result
yy	DATEPART('yy', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays a number representing the year from a stored date value.	11/01/2009	2009
yyyy	DATEPART('yy', [Shell and Tube Heat Exchanger].[Asset Installation Date])
year	DATEPART('year', [Shell and Tube Heat Exchanger].[Asset Installation Date])
q	DATEPART('q', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays a number representing the quarter of the year based on a stored date value.	11/01/2009	4
qq	DATEPART('qq', [Shell and Tube Heat Exchanger].[Asset Installation Date])
quarter	DATEPART('quarter', [Shell and Tube Heat Exchanger].[Asset Installation Date])
m	DATEPART('m', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays a number representing the month from a stored date value.	11/01/2009	11
mm	DATEPART('mm', [Shell and Tube Heat Exchanger].[Asset Installation Date])
month	DATEPART('month', [Shell and Tube Heat Exchanger].[Asset Installation Date])
wk	DATEPART('wk', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays a number representing the week of the month based on a stored date value.	11/01/2009	1
ww	DATEPART('ww', [Shell and Tube Heat Exchanger].[Asset Installation Date])
week	DATEPART('week', [Shell and Tube Heat Exchanger].[Asset Installation Date])
d	DATEPART('d', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays a number representing the day from a stored date value.	11/01/2009	1
dd	DATEPART('dd', [Shell and Tube Heat Exchanger].[Asset Installation Date])
day	DATEPART('day', [Shell and Tube Heat Exchanger].[Asset Installation Date])
dw	DATEPART('dw', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays a number representing the day of the week based on a stored date value.	11/01/2009	1 (i.e., Sunday)
weekday	DATEPART('weekday', [Shell and Tube Heat Exchanger].[Asset Installation Date])		11/01/2009	1 (i.e., Sunday)
y	DATEPART('y', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays a number representing the day of the year based on a stored date value.	11/01/2009	305
dy	DATEPART('dy', [Shell and Tube Heat Exchanger].[Asset Installation Date])
dayofyear	DATEPART('dayofyear', [Shell and Tube Heat Exchanger].[Asset Installation Date])
hh	DATEPART ('hh', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays only the hour from a stored date value.	11/01/2009 4:00:00	4
hour	DATEPART ('hour', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays only the hour from a stored date value.	11/01/2009 4:00:00	4
n	DATEPART ('n', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays only the minutes from a stored date value.	11/01/2009 4:00:00	00
mi	DATEPART ('mi', [Shell and Tube Heat Exchanger].[Asset Installation Date])
minute	DATEPART ('minute', [Shell and Tube Heat Exchanger].[Asset Installation Date])
s	DATEPART ('s', [Shell and Tube Heat Exchanger].[Asset Installation Date])	Displays only the seconds from a stored date value.	11/01/2009 4:00:00	00
ss	DATEPART ('ss', [Shell and Tube Heat Exchanger].[Asset Installation Date])
second	DATEPART ('second', [Shell and Tube Heat Exchanger].[Asset Installation Date])

About Number/Mathematical Functions

A number/mathematical function is used to manipulate values returned on numeric fields.

Tip: For more information on functions, refer to MetaSQL Functions.

About the DECODE Function

A DECODE function is used to evaluate a value in the query results and, based on that evaluation, trigger an action or return a different value.

Example: DECODE Function

Suppose that, in your organization, you have open and closed work history events. Each Work History record contains an Order System Status field, which is used to record the status of that work history event. You want to see how many open work history events you have and how many closed work history events you have. Instead of the stored values of CLSD TECO or OPEN, however, you want to see the values Closed or Open in your query results.

In addition, you are concerned that not all Work History records contain a value in the Order System Status field. You also want to see in your query results which records do not have a value in this field so that you can update those records.

In this case, you might configure a query on the Work History family, and then add the Expr and Order System Status fields to the query.

In this example, to return the status of each work history event as Closed, Open, or No Status (meaning that the record does not contain a value in the Order System Status field), you can configure an expression using the DECODE function.

In this case, the expression syntax is:


                    Decode([Work History].[Order System Status], 'CLSD TECO', 'Closed', 'OPEN', 'Open', 'No Status')

This syntax indicates that for Work History records that contain the value CLSD TECO in the Order System Status field, you want to return the value Closed. For those records with the value OPEN in the Order System Status field, you want to return the value Open. For records where the Order System Status field is empty, you want to return the value No Status.

You would also add a COUNT function on the Order System Status field so that you can see the number of work history events that fall into each category.

Other possible uses include:

You want to review the types of maintenance activities that are being performed in your plant and the total number of failures that resulted in each type of activity. Users can enter any value they choose into a field that tracks maintenance activities, so you know that your users are using different terminology to mean the same thing. For example, to indicate that they replaced broken components, different users might enter replace, replacing, or replacement. You can write a DECODE statement to indicate that the values replace, replacing, and replacement should return the value Replace in the query results.
Members of management want to investigate failures that resulted in a failure cost of $50,000 or more. You can write a DECODE statement to indicate that failures with a failure cost greater than or equal to $50,000 should return the value Please Investigate in the query results. Other failures should return the value OK in the query results.

MetaSQL Functions

Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value. Use of the OVER clause converts the function into an Analytic Function.

AVG
COUNT
MAX
MIN
SUM

Meridium Functions

Meridium functions are supported regardless of what database management system you use. The MetaSQL compiler will automatically convert the expression to a native SQL expression.

Function Syntax:

<meridium_function> ::=
{
  function_id ( [ { <expression> [,...n] } | * ] )
}

...where function_id is the name of the function you are executing.

The following functions are supported:

UserKey

Gets the key of the current logged-in user.

SELECT UserKey()
FROM <source>

Str

Converts the designated field to a VARCHAR value.

-- Syntax
SELECT Str(<expression> [, <size>]) "Expr" FROM <source>

-- Simple Example
SELECT [MI_ACTION].ENTY_KEY "ENTY_KEY"
, Str([MI_ACTION].ENTY_KEY) "Str Enty Key"
FROM [MI_ACTION]

-- Specify the size
SELECT [MI_ACTION].ENTY_KEY "ENTY_KEY"
, Str([MI_ACTION].ENTY_KEY, 20) "Str Enty Key"
FROM [MI_ACTION]

IsNull

Evaluates an expression. If the value is null, evaluates the expression in the second argument and returns its result.

Note: Both arguments must return the same data type.

-- Syntax
SELECT IsNull(<expression>, <expression>) "Expr" FROM <source>

-- Example (Note the conversion of number to string ensuring consistent data types)
SELECT [MI_ACTION].[MI_ACTION_ID_C] "Action ID"
, [MI_ACTION].[MI_ACTION_SHORT_DESC_C] "Name"
, IsNull(Str([MI_ACTION].[MI_ACTION_RESOURCE_COST_N]), 'No Cost Data') "Cost"
FROM [MI_ACTION]

Now

Returns the DBMS system date.

-- Syntax
SELECT Now()
FROM <source>

LocalizedCaption

Returns a localized Family Caption for a given Family Key and User Key.

-- Syntax
SELECT LocalizedCaption(<family_key>, <user_key>)
FROM <source>

-- Example
SELECT mi_families.FMLY_CAPTION_TX "Caption"
, LocalizedCaption(mi_families.FMLY_KEY, 64251708261) "Localized Caption"
FROM mi_families

Decode

Returns a given output based on one or more possible inputs.

-- Syntax
SELECT DECODE(<field_id>, <input_value>, <output_value> [ , <input_value>, <output_value> ], <default_value> )
FROM <source>

-- Example
SELECT [MI_ACTION].[MI_ACTION_ID_C] "Action ID"
, [MI_ACTION].[MI_ACTION_SHORT_DESC_C] "Name"
, [MI_ACTION].[MI_ACTION_RESOURCE_COST_N] "Cost"
, Decode([MI_ACTION].[MI_ACTION_RESOURCE_COST_N], 100, 'Cheap', 10000, 'Moderately Expensive', 100000, 'Expensive', 'Misc') "Cost Category"
FROM [MI_ACTION]
ORDER BY "Cost" Desc

DatePart

Returns the specified part of a date/time value. DatePart and MI_DatePart are equivalent. The return is a number.

--Syntax
SELECT DatePart( <date_part>, <expression> )
FROM <source>

-- Example
SELECT [MI_ACTION].[MI_ACTION_ID_C] "Action ID"
, [MI_ACTION].[MI_ACTION_SHORT_DESC_C] "Name"
, [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR] "Target Completion Date"
, DatePart('yy', [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR]) "Year"
, DatePart('mm', [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR]) "Month"
, DatePart('dd', [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR]) "Day"
, DatePart('hh', [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR]) "Hour"
, DatePart('mi', [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR]) "Minute"
, DatePart('ss', [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR]) "Second"
, DatePart('dw', [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR]) "Day of the week"
, DatePart('qq', [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR]) "Quarter"
, DatePart('dy', [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR]) "Day of the year"
, DatePart('ww', [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR]) "Week"
FROM [MI_ACTION]
WHERE [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR] IS NOT NULL

The date_part can contain any of the following values:

‘quarter’, ‘qq’, ‘q’ - Quarter of the year, 1 - 4
‘year’, ‘yyyy’, ‘yy’ - Year, 4 digits (always)
‘month’, ‘mm’, ‘m’ - Month of the year, 1 - 12
‘day’, ‘dd’, ‘d’ - Day of the month, 1 - 31
‘hour’, ‘hh’, ‘h’ - Hour of the day, 0 - 23
‘minute’,’mi’, ‘n’ - Minute of the hour, 0 - 59
‘second’, ‘ss’, ‘s’ - Second of the minute, 0 - 59
‘dayofyear’, ‘dy’, ‘y’ - Day of the year
‘dayofweek’, ‘weekday’, ‘dw’ - Day of the week, 1 - 7, Sunday is 1
‘weekofyear’, ‘week’, ‘ww’ - Week of the year
‘weekofmonth’, ‘wk’ - Week of month

DateAdd

Adds the specified number of units to a given date/time and returns a new date/time value. DateAdd and MI_DateAdd are equivalent. On Oracle databases, adding years and months is not supported.

-- Syntax
SELECT DateAdd( <date_part>, <num_const>, <expression> )
FROM <source>

-- Example
SELECT [MI_ACTION].[MI_ACTION_ID_C] "Action ID"
, [MI_ACTION].[MI_ACTION_SHORT_DESC_C] "Name"
, [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR] "Target Completion Date"
, DateAdd('yy', 10, [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR]) "Add 10 Years"
, DateAdd('mm', 10, [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR]) "Add 10 Months"
, DateAdd('dd', 10, [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR]) "Add 10 Days"
, DateAdd('hh', 10, [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR]) "Add 10 Hours"
, DateAdd('mi', 10, [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR]) "Add 10 Minutes"
, DateAdd('ss', 10, [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR]) "Add 10 Seconds"
FROM [MI_ACTION]
WHERE [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR] IS NOT NULL

The date_part can contain one of the following values:

‘year’, ‘yyyy’, ‘yy’ - Year
‘month’, ‘mm’, ‘m’ - Month
‘day’, ‘dd’, ‘d’ - Day
‘hh’, ‘h’ - Hour
‘minute’, ‘mi’, ‘n’ - Minute
‘second’, ‘ss’, ‘s’ - Second

The num_const is the number of units that will be added to the provided date.

DateFormat

Returns a date/time value formatted as a string.

--Syntax
SELECT DateFormat( <date_format>, <expression> )
FROM <source>

-- Example
SELECT [MI_ACTION].[MI_ACTION_ID_C] "Action ID"
, [MI_ACTION].[MI_ACTION_SHORT_DESC_C] "Name"
, [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR] "Target Completion Date"
, DateFormat('yyyy-mm-dd', [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR]) "Date"
FROM [MI_ACTION]
WHERE [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR] IS NOT NULL

The date_format can contain any reasonable combination of the following values:

‘yyyy’ - four digit year
‘yyy’ - last 3 digits of the year
‘yy’ - last 2 digits of the year
‘y’ - last digit of the year
‘mm’ - month of the year, 01 - 12
‘mon’ - abbrreviated month name
‘dd’ - Day of the month, 01 - 31
‘ddd’ - Day of the year, 001 - 366
‘d’ - Day of the week, 1 - 7, Sunday is 1
‘dy’ - abbrreviated day name
‘hh’ - Hour of the day, 01 - 12
‘hh12’ - Hour of the day, 01 - 12
‘hh24’ - Hour of the day, 00 - 23
‘mi’ - Minute of the hour, 00 - 59
‘ss’ - Second of the minute, 00 - 59
‘ms’ - Millisecond, 000 - 999
‘am’ - (or pm) meridiem indicator

Note:

Oracle does not support fractional seconds.
Microsoft does not support day of the year.

DateName

Returns the month or day name for date/time value. Note that this value will not be localized.

--Syntax
SELECT DateName( <date_format>, <expression> )
FROM <source>

-- Example
SELECT [MI_ACTION].[MI_ACTION_ID_C] "Action ID"
, [MI_ACTION].[MI_ACTION_SHORT_DESC_C] "Name"
, [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR] "Target Completion Date"
, DateFormat('day', [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR]) "Day"
FROM [MI_ACTION]
WHERE [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR] IS NOT NULL

The date_format can contain any of the following values:

‘month’, ‘mm’, ‘m’ - Month of the year
‘day’, ‘dd’, ‘weekday’, ‘dw’ - Day of the week

UTC

Returns the formatted string equivalent of the argument date/time value. The return will be formatted ‘yyyy-mm-dd hh:mi:ss’.

--Syntax
SELECT UTC(<expression> )
FROM <source>

-- Example
SELECT [MI_ACTION].[MI_ACTION_ID_C] "Action ID"
, [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR] "Target Completion Date"
, UTC([MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR]) "UTC Date"
FROM [MI_ACTION]
WHERE [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR] IS NOT NULL

DateDiff

Returns the difference between to date/time values as number. The argument determines the unit of measure returned. The expression1 argument is subtracted from expression2.

--Syntax
SELECT DateDiff( <date_part>, <expression1>, <expression2>)
FROM <source>

-- Example
SELECT [MI_ACTION].[MI_ACTION_ID_C] "Action ID"
, [MI_ACTION].[MI_ACTION_SHORT_DESC_C] "Name"
, [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR] "Target Completion Date"
, DateDiff('day', [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR], [MI_ACTION].LAST_UPDT_DT) "Days"
FROM [MI_ACTION]
WHERE [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR] IS NOT NULL

The date_PART can contain any of the following values:

‘day’, ‘dd’, ‘d’ - Days
‘hour’, ‘hh’, ‘h’ - Hours
‘minute’, ‘mi’, ‘n’ - Minutes
‘second’, ‘ss’, ‘s’ - Seconds

Length

Returns the length of the specified expression.

-- Syntax
SELECT Length( <expression> )
FROM <source>

-- Example
, [MI_ACTION].[MI_ACTION_SHORT_DESC_C] "Name"
, Length([MI_ACTION].[MI_ACTION_SHORT_DESC_C]) "Name Length"
, [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR] "Target Completion Date"
, Str([MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR]) "Target Completion Date String"
, Length(Str([MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR])) "Target Completion Date Length"
, (1000000 + 2689432) "Test"
, Length((1000000 + 2689432)) "Test Length"
FROM [MI_ACTION]

Note: When determining string length of a Date/Time value, the results may not be as expected. To ensure predictable results, cast the Date/Time to a string with a known format prior to passing it into the Length() function.

CastChar, CastNu,, CastDate

Takes an expression and attempts to return a value cast to the desired type.

-- Syntax
SELECT CastChar(<expression>, <size>)
FROM <source>

SELECT CastNum(<expression>)
FROM <source>

SELECT CastDate(<expression>, <date_format_string>)
FROM <source>

-- Example
SELECT TOP 1 CastChar(Now()) "Now string"
, CastChar(1234567, 7) "Number string"
, CastNum('1234567') "Number from string"
, CastDate('2009-01-01', 'YYYY-MM-DD') "Date from string"
FROM [MI_ACTION]

The size accepts a <num_const> representing the size of the VARCHAR.

The date_format_string is a format string for the date/time value (i.e. YYYY-MM-DD). This string format needs to be compatible with the underlying DBMS.

IndexOf

Returns the index (1-based) of a given character expression within another character expression.

Note: This function is case-sensitive for Oracle and PostgreSQL, but case-insensitive for SQL.

-- Syntax
SELECT IndexOf( <char_const_to_search>, <char_const_to_find> [, <start_position>] )
FROM <source>

-- Example
SELECT [MI_ACTION].[MI_ACTION_ID_C] "Action ID"
, [MI_ACTION].[MI_ACTION_SHORT_DESC_C] "Name"
, INDEXOF([MI_ACTION].[MI_ACTION_SHORT_DESC_C], 'c') "Index of C"
FROM [MI_ACTION]

Modulus

Returns the modulus (remainder) of exp1 divided by exp2.

-- Syntax
SELECT Modulus( <exp1>, <exp2>)
FROM <source>

Substring

-- Syntax
SELECT Substring( <expression>, <start_pos> [, <end_pos>] )
FROM <source>

-- Example
SELECT [MI_ACTION].[MI_ACTION_ID_C] "Action ID"
, [MI_ACTION].[MI_ACTION_SHORT_DESC_C] "Name"
, Substring([MI_ACTION].[MI_ACTION_SHORT_DESC_C], 2) "Name substring from pos 2"
, Substring([MI_ACTION].[MI_ACTION_SHORT_DESC_C], 2, 6) "Name substring from pos 2-6"
FROM [MI_ACTION]

Upper, Lower

Return the argument string expression in upper or lower case.

-- Syntax
SELECT Upper(<expression>)
FROM <source>

SELECT Lower(<expression>)
FROM <source>

-- Example
SELECT [MI_ACTION].[MI_ACTION_ID_C] "Action ID"
, [MI_ACTION].[MI_ACTION_SHORT_DESC_C] "Name"
, Upper([MI_ACTION].[MI_ACTION_SHORT_DESC_C]) "Upper"
, Lower([MI_ACTION].[MI_ACTION_SHORT_DESC_C]) "Lower"
FROM [MI_ACTION]

LTrim, RTrim, Trim

Return a character expression after removing the leading or trailing white space (or both, for Trim).

-- Syntax
SELECT LTrim(<expression>)
FROM <source>

SELECT RTrim(<expression>)
FROM <source>

-- Example
SELECT [MI_ACTION].[MI_ACTION_ID_C] "Action ID"
, [MI_ACTION].[MI_ACTION_SHORT_DESC_C] "Name"
, ('----|' & '     ' & [MI_ACTION].[MI_ACTION_SHORT_DESC_C] & '     ' & '|----') "Untrimmed Name"
, ('----|' & LTRIM(('     ' & [MI_ACTION].[MI_ACTION_SHORT_DESC_C] & '     ')) & '|----') "LTrimmed Name"
, ('----|' & RTRIM(('     ' & [MI_ACTION].[MI_ACTION_SHORT_DESC_C] & '     ')) & '|----') "RTrimmed Name"
FROM [MI_ACTION]

LPad, RPad

Return a character expression of a specified length after padding with a specified character. A space is the default padchar value.

-- Syntax
SELECT LPad(<expression>, <length> [, <padchar>])
FROM <source>

SELECT RPad(<expression>, <length> [, <padchar>])
FROM <source>

-- Example
SELECT [MI_ACTION].[MI_ACTION_ID_C] "Action ID"
, LPAD(CASTCHAR(DatePart('d', [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR])), 2, ' ') "Day"
FROM [MI_ACTION]

Concat

Concatenates the arguments returning a string. The argument list is a params argument (multiple arguments are supported). At least two arguments are required.

-- Syntax
SELECT Concat(<exp1>, <exp2> [,<expn>])
FROM <source>

SELECT Concat(<exp1>, <exp2>)
FROM <source>

-- Example
SELECT Concat('foo-', [MI_ACTION].[MI_ACTION_ID_C], '-bar') "Action ID"
FROM [MI_ACTION]

Year, Month, Day

Return a part of the specified date expression. For more control or granularity when retrieving date parts, see the DatePart function.

-- Syntax
SELECT Year(<date_expression>)
FROM <source>

SELECT Month(<date_expression>)
FROM <source>

SELECT Day(<date_expression>)
FROM <source>

-- Example
SELECT [MI_ACTION].[MI_ACTION_ID_C] "Action ID"
, [MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR] "Target Completion Date"
, YEAR([MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR]) "Target Completion Year"
, MONTH([MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR]) "Target Completion Month"
, DAY([MI_ACTION].[MI_ACTION_TARGE_COMPL_DATE_CHR]) "Target Completion Day"
FROM [MI_ACTION]

SysDate

Retrieves the system date.

-- Syntax
SELECT SysDate()
FROM <source>

-- Example
SELECT [MI_ACTION].[MI_ACTION_ID_C] "Action ID"
, SYSDATE() "System Date"
FROM [MI_ACTION]

LastDate

Returns a date representing the last day of the month in which a given date occurs.

-- Syntax
SELECT LastDate(<const_char>)
FROM <source>

-- Example
SELECT [MI_ACTION].[MI_ACTION_ID_C] "Action ID"
, LASTDATE('2016-02-01') "Last Day of Feb, 2016"
, LASTDATE('2017-02-01') "Last Day of Feb, 2017"
, LASTDATE('2018-02-01') "Last Day of Feb, 2018"
, LASTDATE('2019-02-01') "Last Day of Feb, 2019"
, LASTDATE('2020-02-01') "Last Day of Feb, 2020"
FROM [MI_ACTION]

cost_char must be formatted as YYYY-MM-DD.

Case

Performs a series of evaluations and returns the first result that evaluates to True. The simple syntax works well for an EQUALS comparison. However, if more complexity is required, you can use the general CASE expression to perform various types of comparisons.

-- Syntax (simple CASE expression)
SELECT CASE <field_id>
  WHEN <expression> THEN <expression>
  [ ELSE <expression> ]
END
FROM <source>

-- Example
SELECT [MI_ACTION].[MI_ACTION_ID_C] "Action ID"
, [MI_ACTION].[MI_ACTION_DESCRIPTION_T] "Description"
, CASE [MI_ACTION].[MI_ACTION_DESCRIPTION_T]
   WHEN 'Perform check of lubricant, add or change oil when needed' THEN 'Check'
   WHEN 'Perform changeout of lubricant' THEN 'Changeout'
ELSE 'Other'
END "Lubricant Action"
FROM [MI_ACTION]
WHERE [MI_ACTION].[MI_ACTION_DESCRIPTION_T] LIKE '%Lubricant%'


-- Syntax (general CASE expression)
SELECT CASE
  WHEN <boolean_expression> THEN <expression>
  [ ELSE <expression> ]
END
FROM <source>

-- Example
SELECT [MI_ACTION].[MI_ACTION_ID_C] "Action ID"
, [MI_ACTION].[MI_ACTION_DESCRIPTION_T] "Description"
, CASE
   WHEN [MI_ACTION].[MI_ACTION_DESCRIPTION_T] LIKE 'Perform check of lubricant%' THEN 'Check'
   WHEN [MI_ACTION].[MI_ACTION_DESCRIPTION_T] LIKE 'Perform changeout%' THEN 'Changeout'
ELSE 'Other'
END "Lubricant Action"
FROM [MI_ACTION]
WHERE [MI_ACTION].[MI_ACTION_DESCRIPTION_T] LIKE '%Lubricant%'

Sign

Returns the sign of the numeric argument (-1, 0, +1).

-- Syntax
SELECT Sign(<exp1>)
FROM <source>

Abs

Returns the absolute value of the numeric the argument.

-- Syntax
SELECT abs(<exp1>)
FROM <source>

Reverse

Returns the reverse order of a string value.

-- Syntax
SELECT Reverse(<exp1>)
FROM <source>

Floor

Returns the largest integer less than or equal to the argument numeric expression.

-- Syntax
SELECT Floor(<exp1>)
FROM <source>

Round

Returns the nearest integer for the argument numeric expression. Rounds to the precision, if specified.

-- Syntax
SELECT Round(<exp1> [, <precision>])
FROM <source>

Analytic Functions

Analytic functions are used for aggregating data on a row-by-row basis. They work similar to aggregate functions, except they can return multiple rows of results for each group. They are useful for calculating running totals, moving percentages, and so on.

Analytic functions work the same way, regardless of the database management system. For more information on analytic functions, refer to https://learn.microsoft.com/en-us/sql/t-sql/functions/analytic-functions-transact-sql?view=sql-server-2017.

Syntax:

<analytic_function> ::= function_id() [OVER ( [PARTITION BY field_id] ORDER BY <order_by_expression> )]

...where function_id is the name of the function you are executing, field_id is the field used to group results, and <order_by_expression> specifies which fields must be used to sort the results in the current partition.

The following functions are supported:

SUM

SELECT SUM([FIELD]) OVER(PARTITION BY [FIELD], [FIELD]... ORDER BY [FIELD] ASC, [FIELD] DESC... ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) "Cost" FROM [FAMILY]

MAX

SELECT MAX([FIELD]) OVER(PARTITION BY [FIELD], [FIELD]... ORDER BY [FIELD] ASC, [FIELD] DESC... ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) "Cost" FROM [FAMILY]

AVG

SELECT AVG([FIELD]) OVER(PARTITION BY [FIELD], [FIELD]... ORDER BY [FIELD] ASC, [FIELD] DESC... ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) "Expr" FROM [FAMILY]

COUNT

SELECT COUNT([FIELD]) OVER(PARTITION BY [FIELD], [FIELD]... ORDER BY [FIELD] ASC, [FIELD] DESC... ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) "Expr" FROM [FAMILY]

ROW_NUMBER

SELECT ROW_NUMBER() OVER(PARTITION BY [FIELD], [FIELD]... ORDER BY [FIELD] ASC, [FIELD] DESC...) "Row #" FROM [FAMILY]

CUME_DIST

SELECT CUME_DIST() OVER ([PARTITION BY [FIELD], [FIELD]...] ORDER BY [FIELD] ASC, [FIELD] DESC...) "Expr" FROM [FAMILY]

PERCENT_RANK

SELECT PERCENT_RANK([FIELD]) OVER(PARTITION BY [FIELD], [FIELD]... ORDER BY [FIELD] ASC, [FIELD] DESC... ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) "Expr" FROM [FAMILY]

PERCENTILE_DISC

SELECT PERCENTILE_DISC([FIELD]) OVER(PARTITION BY [FIELD], [FIELD]... ORDER BY [FIELD] ASC, [FIELD] DESC... ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) "Expr" FROM [FAMILY]

PERCENTILE_CONT

SELECT PERCENTILE_CONT([FIELD]) OVER(PARTITION BY [FIELD], [FIELD]... ORDER BY [FIELD] ASC, [FIELD] DESC... ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) "Expr" FROM [FAMILY]

NTILE

SELECT NTILE(num_const) OVER(PARTITION BY [FIELD], [FIELD]... ORDER BY [FIELD] DESC) "Expr" FROM [FAMILY]

num_const is a positive integer representing the number of groups that will be created in the result

Using Max, Min, Avg, Count, and Sum

SELECT DISTINCT [MI_MRBIANAL].[MI_CRITANAL_RSK_CAT_C] "Risk Category"
, Min([MI_MRBIANAL].[MI_CRITANAL_PROB_OF_FAIL_UP_C])OVER ( PARTITION BY [MI_MRBIANAL].[MI_CRITANAL_RSK_CAT_C] ) "Min Prob of Fail"
, Max([MI_MRBIANAL].[MI_CRITANAL_PROB_OF_FAIL_UP_C])OVER ( PARTITION BY [MI_MRBIANAL].[MI_CRITANAL_RSK_CAT_C] ) "Max Prob of Fail"
, Avg([MI_MRBIANAL].[MI_CRITANAL_INSPE_PRIOR_UP_N])OVER ( PARTITION BY [MI_MRBIANAL].[MI_CRITANAL_RSK_CAT_C] ) "Avg Insp Priority"
, Count([MI_EQUIP000].ENTY_ID)OVER ( PARTITION BY [MI_MRBIANAL].[MI_CRITANAL_RSK_CAT_C] ) "# Assets"
, Sum([MI_MRBIANAL].[MI_CRITANAL_LEAK_QUANTITY_N])OVER ( PARTITION BY [MI_MRBIANAL].[MI_CRITANAL_RSK_CAT_C] ) "Total Leak Quantity"
FROM {MIR_HSRBICMP} 
 JOIN [MI_CCRBICOM] ON {MIR_HSRBICMP}.SUCC_ENTY_KEY = [MI_CCRBICOM].ENTY_KEY 
 JOIN {MIR_HSRBICMP} Has_RBI_Components1 ON [MI_CCRBICOM].ENTY_KEY = Has_RBI_Components1.SUCC_ENTY_KEY 
 JOIN {MIR_RBICRAN} ON [MI_CCRBICOM].ENTY_KEY = {MIR_RBICRAN}.PRED_ENTY_KEY 
 JOIN [MI_EQUIP000] ON Has_RBI_Components1.PRED_ENTY_KEY = [MI_EQUIP000].ENTY_KEY 
 JOIN [MI_MRBIANAL] ON {MIR_RBICRAN}.SUCC_ENTY_KEY = [MI_MRBIANAL].ENTY_KEY
ORDER BY [MI_MRBIANAL].[MI_CRITANAL_RSK_CAT_C] Asc

Results:


Risk Category	Min Prob of Fail	Max Prob of Fail	Avg Insp Priority	# Assets	Total Leak Quantity
	1	2	0	615	4
HIGH	1	2	2.425675675675676	148	1,216,204.004371306
LOW	3	5	22.43975903614458	166	590,251.1253449451
MEDIUM	2	5	16.331818181818182	220	813,327.89638713
MEDIUM HIGH	1	4	8.489247311827956	186	1,305,554.6602732195

Additional Meta-SQL Functions

Meta-SQL constructs are used in functions that pass SQL strings.

The following table provides a list of Meta-SQL functions that you can use in query expressions in APM.


Description	Meta-SQL Function	Construction
Return the local time of the database server.	SysDate	SysDate
Evaluate one or more When expressions and return the appropriate Then expression. The Else condition is optional. The End statement is required. This function returns the datatype thenexpression. Note: This function is not a selectable option in the Design workspace, but you can enter the function directly in the SQL workspace.	Case	Case evalexpression When whenexpression Then thenexpression [When whenexpression Then thenexpression] [Else thenexpression] End Evalexpression: Any column or literal value. Whenexpression: Must be same datatype of evalexpression. Thenexpression: All must be the same datatype. Does not have to be the same datatype as evalexpression or whenexpression.

Modulus Function

SELECT  Modulus(ROUND([AQA REG All Fld Types].[ARAQA_REG_ALL_FLD_TY_NUMER_NBR]), 2) "Modulus2"
, Modulus(ROUND([AQA REG All Fld Types].[ARAQA_REG_ALL_FLD_TY_NUMER_NBR]), 3) "Modulus3"
FROM [AQA REG All Fld Types]

About Adding Hyperlinks to a Query

For each column in a query, you can configure one or more URLs to display as hyperlinks in the query results. When a user runs the query, the APM system will build the URLs as needed, passing data from the query results into the URL parameters if necessary, and will display in the query results hyperlinks that the user can select to access the associated feature or perform the associated function.

For example, you might define a URL on a field to an external website. When a user runs the query, the APM system will build a hyperlink from that URL and display the link to the user in the query results. The user will be able to select the hyperlink to open the website in a new browser tab.

If you configure only one URL for a field, when you run the query, that URL will be used to build a single hyperlink in that cell of the query results. If you have not modified the Field cell, whatever values are found in the database for that column will serve as the text for each hyperlink. For example, if an Asset ID column contains a URL to open a record in the Record Manager, when you run the query, the actual Asset IDs retrieved by the APM system will appear as hyperlinks in the results.