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.

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.

The query template grid shows a list of the query templates of the selected query type and their properties.

Query Template Grid Right-Click Menu

Make sure the correct query template is selected before right-clicking.

Change Query Type: Opens Select New Query Type.

Select New Query Type

Query Type: Open the drop-down menu to select a query type.

Type Name: If Legacy 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.

Enter values directly in the grid or in the query template’s property fields.

Query Template Properties

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.

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.

Query Template Script

Enter the native script, with tokens in curly brackets to dynamically retrieve variable information.

Available Tokens:

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

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 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 and MDE Configuration.

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.

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.

Grade Run Query Template

Output Columns

0 = Grade Name
1 = Start Time (Nullable, if null start = query start)
2 = End Time (Nullable, if null end = query end)

Parameters

StartDate or StartTime
EndDate or EndTime

Sample

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

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

StartDate or StartTime
EndDate or EndTime

Optional:
          Grade – Should return all grades if not specified

Sample

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

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

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

0 =  Filter Identifier or NULL
1 = Start Time (Nullable, if null start = query start)
2 = End Time (Nullable, if null end = query end)

Parameters

StartDate or StartTime
EndDate or EndTime

Grade List Template

Output Columns

0 = Grade Name
1 = Description

Optional Parameter

GRADETYPE

Sample

select grade, gradeName as description From GradeBook_Grades Where Status = 0

Grade Groups Template

Output Columns

0 = Group Name
1 = Group Description (nullable)
2 = Grade Name
3 = Grade Description (nullable)

Optional Parameter

GRADETYPE

Spec Limit Template

Output Columns

0 = Lower Limit
1 = Target
2 = Upper Limit

Parameters

StartDate or StartTime
EndDate or EndTime
Grade (optional)
TAGID – not including location.source

Sample 1

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

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.

If using the Relative Cell reference token in your query template:

Then when configuring MDE to use that list you will see:

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.

Home Tab

Use the ribbon commands to step through a test execution of the script and save any resulting changes.

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.

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

SQL Script

Shows the script with test values substituted for the parameters.

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.

Execution Results

Shows the results of the query test execution.

Messages

Displays messages associated with the execution of the script.

If an error occurs, a popup window will describe the error.