--- title: "Query Template" slug: "interface-query-template" updated: 2024-02-07T09:43:08Z published: 2024-02-07T09:43:08Z canonical: "kb.dataparc.com/interface-query-template" --- > ## Documentation Index > Fetch the complete documentation index at: https://kb.dataparc.com/llms.txt > Use this file to discover all available pages before exploring further. # Query Template Use the query template tab to view and create generic SQL script templates that can be used by various PARCview displays. These templates are generally used for overriding a process area’s source of grade, product, filter, and tag limit information. For example, Centerlines can use a query template to retrieve grade run information instead of using a grade tag. Templates can be created and tested here. ![](https://cdn.document360.io/59db2ca4-e512-446a-a143-a7c626447812/Images/Documentation/image086_004.jpg) ### Query Template Grid Use the query template grid to view the list of query templates, filtered by query type. Open the **Query Type** drop-down menu to select a [query type](/docs/interface-query-template#query-types). ![](https://cdn.document360.io/59db2ca4-e512-446a-a143-a7c626447812/Images/Documentation/image088_003.jpg) The query template grid shows a list of the query templates of the selected query type and their [properties](/docs/interface-query-template#query-template-properties). ![](https://cdn.document360.io/59db2ca4-e512-446a-a143-a7c626447812/Images/Documentation/image090_002.jpg) ### Query Template Grid Right-Click Menu Make sure the correct query template is selected before right-clicking. ![](https://cdn.document360.io/59db2ca4-e512-446a-a143-a7c626447812/Images/Documentation/image092_002.jpg) **Change Query Type:** Opens Select New Query Type. ### Select New Query Type ![](https://cdn.document360.io/59db2ca4-e512-446a-a143-a7c626447812/Images/Documentation/image093_001.png) **Query Type:** Open the drop-down menu to select a [query type](/docs/interface-query-template#query-types). **Type Name:** If [Legacy](/docs/interface-query-template#legacy-templates) is selected, the type name must be entered. ### Create New Template Click the **Create New Template** button to add an empty row to the bottom of the list. ![](https://cdn.document360.io/59db2ca4-e512-446a-a143-a7c626447812/Images/Documentation/image095_001.jpg) Enter values directly in the grid or in the query template’s property fields. ![](https://cdn.document360.io/59db2ca4-e512-446a-a143-a7c626447812/Images/Documentation/image097_001.jpg) ### Query Template Properties ![](https://cdn.document360.io/59db2ca4-e512-446a-a143-a7c626447812/Images/Documentation/image099_001.jpg) **Name:** The name of the template. This is the name that will appear in drop-down menus throughout PARCview. **Description:** A description of the template (optional). **DSN:** The data source name for the template. This defines the database connection the query template will use. If left blank, the script will use PARCview’s Default DSN, defined in [Ribbon](/v1/docs/interface-ribbon). **Enabled:** Whether or not the query template is enabled throughout PARCview. Temporarily disabling a query template can be used an alternative to deleting the template. **Test DSN:** Test the connection string specified in the DSN field. **Test Execute:** Opens [Query Test Execute](/docs/interface-query-template#query-test-execute). ### Query Template Script Enter the native script, with tokens in curly brackets to dynamically retrieve variable information. ![](https://cdn.document360.io/59db2ca4-e512-446a-a143-a7c626447812/Images/Documentation/image101_001.jpg) **Available Tokens:** | Location | This value is supplied by the Location portion of the UTag whose data is being retrieved, e.g. *Location*.Source.TagID/TagSwitch | | --- | --- | | Source | This value is supplied by the Source portion of the UTag whose data is being retrieved, e.g. Location.*Source*.TagID/TagSwitch | | TagId | This value is supplied by the Tag Id portion of the UTag whose data is being retrieved, e.g. Location.Source.*TagID*/TagSwitch | | StartTime | The start time of a tag data request | | StartDate | Same as StartTime | | EndTime | The end time of a tag data request | | EndDate | Same as EndTime | | UserID* | The value in the UserID field in this source’s configuration. | | Password* | The value in the Password field in this source’s configuration. | | LimitType | A numeric value used by the Limit script corresponding to the type of limit being requested, returns: 1 = Control, 2 = Spec, 3 = Operator | | BasisTime | Start datetime of the grade specified in BasisValue | | BasisValue | The value that specifies for which grade to retrieve limits | | AggregateType | Which aggregate to calculate, retrieved from tag switch | | StepInterval | Aggregate step interval in seconds, retrieved from tag switch | | Resolution | Current plot resolution, set by the trend | | ProjTime | Projection start time, uses Now time in trend | | DataType | Data type as returned by Tag Info query | | BoundsOn | Shows whether Include Bounds is True (1) or False (0) | | TimeStamp | Used by PARCview to specify the timestamp of a single value in the Insert, Update and Delete scripts | | WriteValue | Used by PARCview to specify a value in the Insert and Update scripts | | WriteQuality | Used by PARCview to specify quality in the Insert and Update scripts | | CellValue:"UTag" | For MDE Lookup Queries. An explicit cell reference, where “UTag” is the Utag name of a cell in the MDE sheet. When the query executes, the value in the cell is retrieved and used in the query. Limits the Query template list to one MDE sheet. | | CellValue: {Param} | For MDE Lookup Queries. A relative Cell reference, where {Param} appears in the query definition as a user-defined parameter. Places Utag Name of a cell in the MDE sheet. When the query executes, the value in the cell is retrieved and used in the query. Allows a general List query to be used in multiple MDE sheets. | **If a source does not require a user name or password for making a connection, the UserID and Password fields can be used to pass different values into a query template.* ### Date/Time Parameter Format Modify the date/time parameter format settings to make sure that the datetime values supplied by the script match the format expected by the target SQL Server. ![](https://cdn.document360.io/59db2ca4-e512-446a-a143-a7c626447812/Images/Documentation/image103_002.jpg) **Format:** Enter a custom date and time format using standard Windows format specifiers. See MSDN entry: [https://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.110).aspx](https://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.110).aspx) for more information. **Test Value:** Select a sample time from the date picker. **Test Result:** The result of the format applied to the test value. ### Query Types Query templates can be organized by query type. The query types are used in the [Run Browser](/v1/docs/tools-run-browser) and MDE Configuration. ![Graphical user interface, text, application Description automatically generated](https://cdn.document360.io/59db2ca4-e512-446a-a143-a7c626447812/Images/Documentation/image105_002.jpg) **Legacy:** This list contains legacy query templates. **Grade Run:** Provides grade, start time, and end time of grade runs. **Product Run:** Provides product, start time, and end time of product runs. **Phase Run:** Provides phase, start time, and end time of phases within grade run. **Filter Run:** Provides start time and end time of period of time to filter out data. **Grade List:** Provides list of grade names and grade descriptions. **Grade Groups:** Provides list of grade group names. **Spec Limit:** Provides tag limit values for the Spec limit type. **MDE Lookup:** Supply values to the Display and Value fields in a MDE Lookup List. ### Legacy Templates Legacy templates were created prior to version 5.5.0.0. When this type is selected, the Type Name column appears in the query template grid. ![Table Description automatically generated](https://cdn.document360.io/59db2ca4-e512-446a-a143-a7c626447812/Images/Documentation/image107_003.jpg) **Centerline:** Legacy query templates for centerline displays. If still applicable, query type should be changed. **Logbook:** Query template for contact list. **Waterfall:** Legacy query templates for waterfall displays. If still applicable, query type should be changed. **REPORT:** Query templates for PARCreports. This query type is still used by PARCreport displays. Change or delete the query type using the context menu. ![Graphical user interface, text, application, table Description automatically generated](https://cdn.document360.io/59db2ca4-e512-446a-a143-a7c626447812/Images/Documentation/image109_002.jpg) ### Grade Run Query Template **Output Columns** ```sql 0 = Grade Name 1 = Start Time (Nullable, if null start = query start) 2 = End Time (Nullable, if null end = query end) ``` **Parameters** ```sql StartDate or StartTime EndDate or EndTime ``` **Sample** ```sql SELECT       g.grade [name],       rm.endTime as startTime, null as endTime from       reelmaster rm,       GradeBook_Grades g,       GradeBook_GradeVersions gv where       rm.gnr = gv.gnr       and gv.gradeID = g.GradeID       and rm.endtime >= '{STARTDATE}'       and rm.endtime <= '{ENDDATE}'       and rm.reelStatus = 0       and rm.deptno = {DEPTNO} order by       startTime ``` ### Product Run Query Template **Output Columns** ```sql 0 = Product Name 1 = Start Time (Nullable, if null start = query start) 2 = End Time (Nullable, if null end = query end) 3 = Grade Name (Optional) ``` **Parameters** ```sql StartDate or StartTime EndDate or EndTime Optional:          Grade – Should return all grades if not specified ``` **Sample** ```sql select  ctc_product.unitname, ctc_product.starttime, ctc_product.endtime, ctc_gradename.name from (((ctc_product INNER JOIN ctc_gradeversion ON ctc_product.gradeversionid = ctc_gradeversion.gradeversionid )  INNER JOIN ctc_process ON ctc_process.processid = ctc_product.processid ) INNER JOIN ctc_gradename ON ctc_gradename.gradenameid = ctc_gradeversion.gradenameid) where ctc_process.name = '{PROCESS UNIT}' and ctc_product.endtime <= #{ENDDATE}# AND ctc_product.endtime  >= #{STARTDATE}#  order by ctc_product.endtime ``` ### Phase Run Template **Output Columns** ```sql 0 = Phase Name 1 = Start Time (Nullable, if null start = query start) 2 = End Time (Nullable, if null end = query end) 3 = Grade Name (Optional) ``` **Parameters** ```sql StartDate or StartTime EndDate or EndTime Optional:         Grade – Should return all grades if not specified         Phase– Should return all phases if not specified ``` ### Filter Run Template **Output Columns** ```sql 0 =  Filter Identifier or NULL 1 = Start Time (Nullable, if null start = query start) 2 = End Time (Nullable, if null end = query end) ``` **Parameters** ```sql StartDate or StartTime EndDate or EndTime ``` ### Grade List Template **Output Columns** ```sql 0 = Grade Name 1 = Description ``` **Optional Parameter** ```sql GRADETYPE ``` **Sample** ```sql select grade, gradeName as description From GradeBook_Grades Where Status = 0 ``` ### Grade Groups Template **Output Columns** ```sql 0 = Group Name 1 = Group Description (nullable) 2 = Grade Name 3 = Grade Description (nullable) ``` **Optional Parameter** ```sql GRADETYPE ``` ### Spec Limit Template **Output Columns** ```sql 0 = Lower Limit 1 = Target 2 = Upper Limit ``` **Parameters** ```sql StartDate or StartTime EndDate or EndTime Grade (optional) TAGID – not including location.source ``` **Sample 1** ```sql Declare @TagName VarChar(250) Declare @Grade INT Declare @tGrade VarChar(50) Select @tGrade = '{Grade}' IF Len(RTRIM(LTRIM(@tGrade))) > 0 BEGIN Select @TagName = '{TagID}' Select @Grade = Cast('{Grade}' as int) Declare @DeptNo Int Declare @PropNo Int Declare @deptNoEnd Int Select @deptnoEnd = CharIndex('.', @tagName, 0) Select @deptNo = cast(substring(@tagname,3,1) as int) Select @PropNo = cast(substring(@tagname,CharIndex('.', @tagName, @deptnoEnd + 1) + 1, len(@tagname) - CharIndex('.', @tagName, @deptnoEnd + 1)  ) as int) ``` **Sample 2** ```sql Select       gs.LR,       gs.Target,       gs.HR       From                  GradeBook_GradeSpecs gs                  INNER JOIN                             GradeBook_GradeVersions gv                             on gs.gnr = gv.gnr                             AND gv.recordDate = (                                          Select max(gv2.recordDate)                                                    from                                                                     gradeBook_GradeVersions gv2                                                                     inner join gradeBook_Grades g2                                                                     on                                                                                gv2.gradeid = g2.gradeID                                                                                and g2.grade = @grade                                                                                and gv2.recordDate <= '{StartDate}'                                                                                             )                                                 inner join                                                             GradeBook_Grades g                                                             on                                                             gv.gradeid = g.gradeID                                                             and g.grade = @grade Where         gs.PropNo = @propNo         and gs.SpecType = 1 END ELSE BEGIN   Select null,null,null END ``` ### MDE Lookup MDE Lookup query templates supply values to the Display and Value fields in a MDE Lookup List. This query can return however many columns you wish, but only the first column will be used for Display Text and Value. This list will be displayed in the order in which it is returned, so your query can return multiple columns and use them for sorting. The current token list for the queries is available for this query, and two new ones have been added. | CellValue:"UTag" | For MDE Lookup Queries. An explicit cell reference, where “UTag” is the Utag name of a cell in the MDE sheet. When the query executes, the value in the cell is retrieved and used in the query. Limits the Query template list to one MDE sheet. | | --- | --- | | CellValue: {Param} | For MDE Lookup Queries. A relative Cell reference, where {Param} appears in the query definition as a user-defined parameter. Places Utag Name of a cell in the MDE sheet. When the query executes, the value in the cell is retrieved and used in the query. Allows a general List query to be used in multiple MDE sheets. | If using the Relative Cell reference token in your query template: ![A screenshot of a computer Description automatically generated](https://cdn.document360.io/59db2ca4-e512-446a-a143-a7c626447812/Images/Documentation/image111_002.jpg) Then when configuring MDE to use that list you will see: ![Graphical user interface, text, application Description automatically generated](https://cdn.document360.io/59db2ca4-e512-446a-a143-a7c626447812/Images/Documentation/image112_002.png) **Example Uses:** - Getting data from an MES system like work orders, for an operator to assign to the cell. - Building chained pick lists, where a second list is dependent on the selection in the first list. - Determine pick lists options based on the selected grade for the record. - Re-use pick lists. With the Fixed list, to have a standard condition list (Great, Good, Bad, Horrible) for condition inspections, you would have to program that list for every inspection tag. With a standard query, you can assign the same list to multiple tags. ### Query Test Execute Use Query Test Execute to supply sample parameter values and test the output and validity of the SQL script. ![](https://cdn.document360.io/59db2ca4-e512-446a-a143-a7c626447812/Images/Documentation/image113_001.png) ### Home Tab Use the ribbon commands to step through a test execution of the script and save any resulting changes. ![](https://cdn.document360.io/59db2ca4-e512-446a-a143-a7c626447812/Images/Documentation/image115.jpg) **Parse Template:** Parses the template script and populates the parameters table. **Create Script:** Replaces parameters with supplied values. **Execute Script:** Executes the SQL script. See [Execution Results](/docs/interface-query-template#execution-results). **Hide Script:** Hides the SQL Script panel. **Horizontal:** Switch to a horizontal stacking style. **Word Wrap:** Enables word wrapping. **Date/Time Parameter Format:** Modify the date/time parameter format settings to make sure that the datetime values supplied by the script match the format expected by the target SQL Server. Enter a custom date and time format using standard Windows format specifiers. See MSDN entry: [https://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.110).aspx](https://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.110).aspx) for more information. **Copy:** Copies the execution results to the Clipboard **Save and Close:** Saves changes and closes the window. **Cancel:** Closes the window without saving changes. ### Template Script Enter the template script here, or copy/paste it in from a program like SQL Server Management Studio. Denote variable parameters by surrounding the parameter name with curly brackets. ![](https://cdn.document360.io/59db2ca4-e512-446a-a143-a7c626447812/Images/Documentation/image117_002.jpg) ### SQL Script Shows the script with test values substituted for the parameters. ![](https://cdn.document360.io/59db2ca4-e512-446a-a143-a7c626447812/Images/Documentation/image119_003.jpg) ### Parameters Parameters allow users to supply values to the query template before its execution. After the template is parsed, enter sample test values in the Value column. ![](https://cdn.document360.io/59db2ca4-e512-446a-a143-a7c626447812/Images/Documentation/image121_003.jpg) ### Execution Results Shows the results of the query test execution. ![](https://cdn.document360.io/59db2ca4-e512-446a-a143-a7c626447812/Images/Documentation/image123_002.jpg) ### Messages Displays messages associated with the execution of the script. ![](https://cdn.document360.io/59db2ca4-e512-446a-a143-a7c626447812/Images/Documentation/image125_001.jpg) If an error occurs, a popup window will describe the error. ![](https://cdn.document360.io/59db2ca4-e512-446a-a143-a7c626447812/Images/Documentation/image126_001.png)