Reference Information: Query Joins, Functions, and Hyperlinks

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.

Example 1: Creating a GE Digital 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 GE Digital 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, GE Digital 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}
                    
                

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
                

Example: GROUP BY Function

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.

Example: LEN Function (SQL Only)

Suppose that you recently implemented a new process for storing Failure IDs in Failure records. Previously, Failure IDs were formatted as FAIL-n, where n indicated the number of the failure. For example, the tenth failure that was recorded contained a Failure ID of FAIL-10. You have decided that you want all failures to contain four digits and that zeroes should be used as placeholders if the failure is not the 1000^th failure or later. For example, the tenth failure should be recorded as FAIL-0010 instead of FAIL-10.

You know that a Failure ID in the new format will contain nine characters, where the dash (-) is considered a character. You want to see which Failure records contain IDs with fewer than nine characters. In this case, you might configure a query on the Failure family, and add the Failure ID field and the ENTY_KEY system field to the query twice each.

In this example, to display each Failure record with a Failure ID that contains fewer than nine characters, you would configure an expression using the LEN function. You would also configure the alias to indicate that the column displays the number of characters in the Failure ID.

In this case, the expression syntax is:

                    LEN([Failure].[Failure ID])
                

The query also includes criteria on this column that indicates that the results should display only Failure records where the Failure ID contains fewer than nine characters.

A hyperlink has been added to the Failure ID field so that you can open from the results each Failure record in the Record Manager and update the Failure ID. Because the query is running in unformatted mode and the hyperlink includes the EntityKey parameter and the FamilyKey parameter, the Entity Key field and the Family Key field are also included in the query.

More Examples of Character Functions

The following table lists more examples of using Character functions:

Example: TO_CHAR Function

Suppose that you want to see only the year in which your shell and tube heat exchangers were installed. All Equipment records in your database contain an Asset Installation Date field, and the values are stored in the format mm/dd/yyyy. You do not want to return the month or day in the query results.

In this case, you might configure a query on the Shell and Tube Heat Exchanger family, and add the Asset ID, Asset Description, and Year Installed fields to the query.

In this example, to return the year in which each shell and tube heat exchanger was installed, you would configure an expression using the TO CHAR function. You would also configure the alias to indicate that the column returns the year the item was installed.

In this case, the expression syntax is:

                    TO CHAR([Shell and Tube Heat Exchanger].[Asset Installation Date], 'yyyy')
                

The syntax indicates that you want to see the Asset Installation Date field of Shell and Tube Heat Exchanger records and convert the returned values into a yyyy format. In other words, instead of seeing the installation dates in their stored format of mm/dd/yyyy, you will see only the year.

Other possible uses include:

• Reformat a numeric cost value so that it represents a currency value (e.g., reformat 125.75 as $125.75)
• Remove the day and year from a date and return only the month (e.g., reformat 12/15/2004 as 12)

The following table lists more examples of using the TO CHAR function to reformat dates.

The following table lists examples of using the TO_CHAR function to reformat numbers.

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.

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.

Example: ADD_MONTHS (Oracle Only)

Suppose that all shell and tube heat exchangers in your facility require inspection every six months. You might want to run a query to see if the last inspection date of all shell and tube heat exchangers and the date that is six months after that date. If Shell and Tube Heat Exchanger records in your database contain an Asset Inspection Date value, you could do so using the ADD_MONTHS function.

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

In this example, to return the next date on which each shell and tube heat exchanger should be inspected, you would configure an expression using the ADD_MONTHS function. You would also configure the alias to indicate that the column returns the next inspection date.

The expression syntax is:

                    ADD MONTHS([Shell and Tube Heat Exchanger].[Asset Inspection Date], 6)
                

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

Example: DATEADD (SQL Only)

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 DATEADD function.

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

In this example, to return the next inspection date, you would configure an expression using the 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:

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

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

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.

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: LAST_DAY (Oracle Only)

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

                    LAST DAY([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: NEXT_DAY (Oracle Only)

After pieces of equipment are installed in your facility, you want to perform a quick check on them the following Monday. If all Equipment records in the database contain the field Installation Date, you could do so using the NEXT DAY function.

You might configure a query on the Equipment field, and add the Equipment ID, Equipment Short Description, Installation Date, and Following Monday fields to the query.

In this example, you would configure an expression using the NEXT DAY function. You would also configure the alias to indicate that the column displays the following Monday.

The expression syntax is:

                    NEXT DAY([Equipment].[Installation Date], 2)
                

The NEXT DAY function assumes that each week begins on Sunday and ends on Saturday. The expression includes a parameter indicating which day of the week you want to see in the results, where the value is between 1 and 7, where 1 represents Sunday and 7 represents Saturday.

In this example, the parameter 2 represents Monday. The syntax indicates that you want to see the date of the first Monday following the date stored in the Installation Date field.

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

Example: MONTHS_BETWEEN

After assets have been installed and have failed for the first time, you want to determine how many months passed between the installation date and the failure date. If all Air Cooled Heat Exchanger records contain the Asset Installation Date field, and all Failure records contain the Failure Date/Time field, then you can do so using the MONTHS BETWEEN 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, Asset Description, Asset Installation Date, Failure ID, Failure Date/Time, and Intervals in Months fields to the query.

In this example, you would configure an expression using the MONTHS_BETWEEN function. You would also configure the alias to indicate that the column returns the number of months between installation and failure.

The expression syntax is:

                    MONTHS BETWEEN([Failure].[Failure Date/Time], [Air Cooled Heat Exchanger].[Asset Installation Date])
                

When you run the query, the values in the Interval in Months column will contain decimal points. A more usable result would show rounded values without decimal points. You should, therefore, also use the TRUNC function so that the Interval in Months column displays only whole numbers. In this case, the entire expression syntax would be:

                    TRUNC(MONTHS BETWEEN([Failure].[Failure Date/Time], [Air Cooled Heat Exchanger].[Asset Installation Date]))
                

Example: DATEDIFF (SQL Only)

After pieces of equipment have been installed and have failed for the first time, you want to determine how many days passed between the installation date and the failure date. If all Air Cooled Heat Exchanger records contain the Asset Installation Date field, and all Failure records contain the Failure Date/Time field, then you can do so using the DATEDIFF 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, Asset Description, Asset Installation Date, Failure ID, Failure Date/Time, and Difference fields to the query.

In this example, you would configure an expression using the DATEDIFF function. You would also configure the alias to indicate that the column displays the difference between the installation date and failure date.

In this case, the expression syntax is:

                    DATEDIFF('day', [Air Cooled Heat Exchanger].[Asset Installation Date], [Failure].[Failure Date/Time])
                

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

Example: TO_DATE (Oracle Only)

Suppose you want to convert the values on the Asset Service field in Air Cooled Heat Exchanger records to dates. For example, the values stored on the Asset Service field look something like the following: 11012009. You want to view this number in date format, like this: 11/01/2009.

In this case, you might configure a query on the Air Cooled Heat Exchanger family, and add the Asset ID, Asset Description, and Asset Service (Date) fields to the query.

In this example, to return the value on the Asset Service field formatted as a date, you would configure an expression using the TO DATE function. You would also configure the alias to indicate that the column returns the value on the Asset Service field as a date.

In this case, the expression syntax is:

                    TO DATE([Air Cooled Heat Exchanger].[Asset Service], 'MMDDYYYY')
                

...where 'MMDDYYYY' corresponds to the order in which the values representing the month (MM), day (DD), and year (YYYY) appear on the Asset Service field. In other words, the values on the Asset Service field appear like this: 11012009, where 11 represents the month (MM), 01 represents the day (DD), and 2009 represents the year (YYYY).

Example: NEW_TIME

Suppose that times are stored in Air Cooled Heat Exchanger Records in Eastern Standard Time, and you want to convert the values to Pacific Standard Time. If Air Cooled Heat Exchanger records contain the Asset Installation Date field, you could do so using the NEW TIME function.

You might configure a query on the Air Cooled Heat Exchanger family, and add the Asset ID, Asset Description, and Installation Date (PST) fields to the query.

In this example, you would configure an expression using the NEW TIME function. You would also configure the alias to indicate that the column displays installation dates in Pacific standard time.

The expression syntax is:

                    NEW TIME([Air Cooled Heat Exchanger].[Asset Installation Date], 'EST', 'PST')
                

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

Example: NVL (Oracle Only)

Suppose that you want to see the serial number of the air cooled heat exchangers in your facility even if the Asset Serial Number field in the Air Cooled Heat Exchanger records is empty, and if the Asset Serial Number field is empty, you want to see the value Not Defined.

In this case, you might configure a query on the Air Cooled Heat Exchanger family, and add the Asset ID, Asset Description, and Serial Numbers (ALL) fields to the query.

In this example, you would configure an expression using the NVL function. You would also configure the alias to indicate that the column returns serial numbers.

In this case, the expression syntax is:

                    NVL([Air Cooled Heat Exchanger].[Asset Serial Number], 'Not Defined')
                

...where Not Defined is the value that will appear where a null value is found on the Asset Serial Number field in the Air Cooled Heat Exchanger records.

Example: ISNULL (SQL Only)

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 (Oracle Only)

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: TRUNC (Oracle Only)

Suppose that you want to see the number of months between when the air cooled heat exchangers were installed and when they failed for the first time, and you want to view that number in a truncated form. For example, instead of seeing the number of months in the format 3.33333, you want to see only 3.

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, Asset Description, Asset Installation Date, Failure ID, Failure Date/Time, and Interval in Months fields to the query.

In this example, you would configure an expression using the TRUNC function and the MONTHS BETWEEN function. You would also configure the alias to indicate that the column returns the interval between installation and failure.

The expression syntax is:

                    TRUNC(MONTHS BETWEEN([Failure].[Failure Date/Time], [Air Cooled Heat Exchanger].[Asset Installation Date]))
                

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.

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.

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.

Modulus Function (SQL Only)

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

Modulus Function (Oracle Only)

SELECT  MOD(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]