MO PACS Query

This documentation describes how to use PACS.Query, an advanced SQL query interface.

PACS.Query allows you to carry out the following activities without an in-depth knowledge of SQL or the PACS architecture:

• Retrieval – You can build queries to retrieve different combinations of data.
• Work Lists – Supervisors can build queries and assign them as work lists to team members.
• Data Export – You can export data retrieved by the queries for printing or use in a spreadsheet or other application.

Advanced Users

In addition to these activities, users with a solid understanding of SQL and a strong knowledge of the PACS architecture can create queries manually or design queries using recommendations for advanced users.

Purpose

Use this procedure if you need to modify an existing connection (for example, to connect to a particular database) or if you need to add a new connection.

To Change an Existing Connection

1. Choose Activities > Queries > Launch Query builder to open PACS.Query.
2. Click Edit Connections.
3. In the Connections box, select the connection you would like to change.
4. Change the entries for the connection as required, and then click Save.

To Add a New Connection

1. Choose Activities > Queries > Launch Query builder to open PACS.Query.
2. Click Edit Connections.
3. Click New.
4. Complete the following fields:
   • Name – Type the name of the database you will connect to.
   • Server – Type the name of the server you will connect to.
   • Environment – Type the environment ID to connect to (typically you type 0). You can use this field to connect to databases designed for specific purposes, such as those designed for testing.
5. If you are an advanced user and need to log on to a domain other than your normal network location, complete the fields Domain, Domain User Name and Domain Password. Then, under SQL Server Connection Info, select User Domain Authentication.
6. If you need to test your connection, complete the fields Application User Name and Application Password. Then click Test Connection.
7. If your facility is set up to use SQL authentication, you must enter the SQL user name and password provided by your system administrator.
8. Click Save.

Procedure

1. In PACS.Query, choose Activities > Queries > Launch Query Builder.
2. Select a connection.
3. Enter the user name and password the system administrator provided to you.
4. If you want to save your user name and password so that you do not have to type it again in the future, select Remember Me.
5. Click Login.

The main PACS.Query window is divided into three panes as shown below.

• Bands – The leftmost pane contains bands that allow you to select fields and tables to be included in queries, or to use queries that you saved previously.
• Query Design Area – The top pane is the area you use to design a query. If you are an advanced user and want to edit SQL code manually, you can use the SQL tab.
• Query Results Area – The bottom pane displays query results after you click Execute SQL to run a query.

Fields Band

The Fields band contains a hierarchical listing of field selections that are commonly used.

The hierarchy is structured with categories at the highest level, followed by subcategories and fields at the lowest level. In some cases, subcategories are nested within subcategories.

When you enable tooltips (Settings > Tooltips Enabled), if you place your cursor over a field name, the tooltip displays the table name to the left of the dot, and the field name to the right of the dot. It also displays a description and sample data.

Important We recommend that users who are not advanced select fields from within a category when creating queries. Only users with advanced knowledge of SQL and the PACS database architecture should select fields across categories.

Tables Band

The Tables band allows advanced users who have a thorough understanding of SQL and the PACS architecture to select fields for a query directly from the PACS database tables.

Clicking the plus sign (+) next to a table name displays the specific fields in a table that you can select to create a query.

My Queries Band

The My Queries band displays queries that you saved previously. You can access and run queries saved previously as often as needed.

You use the query design area to create queries by:

• Choosing the fields to be used in a query.
• Specifying conditions that filter records retrieved by the system.
• Specifying the order in which the system sorts results.

The following options are available in the query design area:

• Select – When the Select check box is selected, the field is included in query results. When the Select check box is clear, the field is included as part of the selection criteria, but its results are not displayed in the results list.
• Table – Identifies which PACS database table the field is selected from.
• Field – Identifies the field name.
• Function – The functions listed in the table below are available for use when creating queries.

• Condition – You use conditions to limit and filter the results returned by a query. To select conditions to be used in the query, click the plus sign button.

  The conditions listed in the table below are available for use when creating queries:

• Sort – The sort options listed in the table below are available for use in creating queries:

• Delete – To delete a field from the query design area, click the Delete button.

Manual SQL Mode

You can click the SQL tab to create SQL statements manually or to edit existing SQL queries.

You must have advanced knowledge of the PACS architecture to produce meaningful queries using SQL mode.

The following menu options are available in PACS.Query.

File Menu

• New Query – Clears the Design tab of field selections and SQL code so that a new query can be designed.
• Save Query – Saves a query within PACS.Query so that you can run it again or modify it at a later time, and so that you can access the query from PACS Appraisal.
• Save As – Saves a query under a different name and description.
• Delete Query – Deletes any of the queries stored in My Queries.
• Save Results – Saves the results of a query so that you or other users can use it in PACS.
• Export Results – Exports the results of a query in tab delimited or CSV format so that it can be viewed or opened by applications such as Microsoft Excel.
• Save Field – Saves field selections to the Fields band of PACS.Query.
• Log out – Ends the PACS.Query session.

Operations Menu

• Left Outer Joins – Changes an individual join to a left outer join. The default join type is an inner join.
• Set Sort Order – Sets the sort order of returned query results.
• Or Operators – Combines where conditions with OR operators.
• Verify SQL – Confirms the syntax of a query.
• Execute SQL – Confirms the syntax of a query, runs the query and returns results.

Settings Menu

• Fonts – Changes font size.
• Join Choice Disabled/Enabled – For advanced users. When enabled, prompts you to select tables to join together in ambiguous situations when a query is verified or executed.
• Tooltips Enabled/Tooltips Disabled – Enables or disables the display of field tooltips. When tooltips are enabled, if you place your cursor over a field name, the tooltip displays the table name to the left of the dot, and the field name to the right of the dot.

The following toolbar options are available in PACS.Query:

• New Query – Clears the Design tab of field selections and SQL code so that a new query can be designed.
• Save Query – Saves a query within PACS.Query so that you can run it again or modify it at a later time, and so that you can access the query from PACS Appraisal.
• Save As – Saves a query under a different name and description.
• Delete Query – Deletes any of the queries stored in My Queries.
• Save Results – Saves the results of a query so that you or other users can use it in PACS.
• Export Results – Exports the results of a query in tab delimited or CSV format so that it can be viewed or opened by applications such as Microsoft Excel.
• Save Field – Saves field selections to the Fields band of PACS.Query.
• Left Outer Joins – Changes an individual join to a left outer join. The default join type is an inner join.
• Set Sort Order – Sets the sort order of returned query results.
• Or Operators – Combines where conditions with OR operators.
• Verify SQL – Confirms the syntax of a query.
• Execute SQL – Confirms the syntax of a query, runs the query and returns results.

Top Count – Limits the number of records displayed to the number selected in this field.

Note The Top Count option limits the number of records displayed for the query, but the system could contain more records than there are displayed.

• Year – The year for which the system will return records.
• Exclude Deleted Properties – Selected by default. When selected, does not include any properties marked as deleted in the query results.

Year Selections

Note If a query specifies values that are different than the selections for Year, Exclude Deleted Properties, and Include Parent Properties in the toolbar, the values in the query overwrite the values in the toolbar.

For example, if the Year field contains a value of 2007, but in the query the prop_val_yr field is selected with a condition for values 2004, 2005, and 2007, the system returns results for all three years specified within the query – 2004, 2005, and 2007.

Recommendation When you select more than one of the year fields sup_tax_yr, prop_val_yr, owner_tax_yr, tax_yr, sup_yr, and exmpt_tax_yr, we recommend that you do the following:

• Select None for the Year toolbar option.
• Specify a condition for only one of the year fields.

Purpose

Use this procedure to create a query to view and/or export data from the system, or to create a work list for other team members to use for carrying out activities from within PACS Appraisal.

You can rerun the query as needed to update the results.

OR Operators

An essential aspect of creating a query involves specifying conditions to limit query results and establish the range of values for a field. Conditions within PACS.Query are combined with AND operators by default, but you have the option to use OR operators as explained in Step 4.

The OR operators option is available only when at least two fields have been specified.

Advanced Users

If you are an advanced user with a solid understanding of SQL as well as knowledge of the PACS database architecture, see Additional Information – Advanced Users below for information about options available to you when creating queries.

Procedure

1. Select fields to be included in the query.
   1. Choose Activities > Queries > Launch QueryBuilder to open PACS.Query. Within either the Fields band or the Tables band, select the fields to be included in the query either by double-clicking on the fields within a category of the hierarchy or by dragging the fields and dropping them in the query design area.

      

   2. If you want to include a field as part of the selection criteria but do not want to display the field in the query results, clear the Select check box. For recommendations about using year fields, see the Additional Information – Year Selections below.
2. If needed, select functions for the fields.

   Note The function specifies how the system retrieves data for the field (for example, adding all values within a field, or selecting the largest value for a field).

3. Click the Add Condition button to select a condition.

   For information about the conditions you can select and the options in the Condition Editor window, see Additional Information – Conditions below.

4. When required, set conditions as OR operators.
   1. Click Or Operators.

      Note Conditions that need to be combined into a single term using the OR operator should be listed sequentially.

   2. In the top pane for OR conditions, do one of the following:
      • To set the condition as an OR operator, select the check box.
      • To leave the condition as an AND operator, leave the check box cleared.
   3. To preview the SQL code as it will be structured according to the OR operators you have set, click Preview.
   4. After completing the settings for your OR operators, click OK.

      Note When you add or remove a field to or from a query that has an OR operator grouping specified, the system automatically resets the OR operator grouping. If necessary, you must then specify the OR operator groupings.

5. Specify the sort order.
   1. Specify that results are sorted in ascending order or descending order, or leave the Sort field set to none so that they are not sorted at all.
   2. If you set the sort order for more than one field, click Set Sort Order to specify the order in which columns are used.
6. If required, select a left outer join. For more information about using joins, see the Additional Information – Joins section below.
   1. Select Left Outer Join. The Left Table Join window is displayed with tables that are available for left table joins.
   2. Select the tables to be included in the left outer join, and then click Save.

      Caution In the following step, selections you make for the Year, Exclude Delete Properties, and Include Parent Properties fields may be overwritten if there are different values for these fields within a query. Values specified within a query overwrite values specified for these fields in the toolbar.

   For example, if the Year field contains a value of 2007, but in the query the prop_val_yr field is selected with a condition for values 2004, 2005, and 2007, the system returns results for all three years specified within the query – 2004, 2005, and 2007.

7. If required, use the following parameters to define query results.
   • Top Count – Limits the number of records returned to the number selected in this field.
   • Year – The year for which the system will return records.
   • Exclude Deleted Properties – Selected by default. When selected, does not include any properties marked as deleted in the query results.
8. If you want to check that the structure and design of the query is correct, click Verify SQL.
9. Click Execute SQL to generate results for the query and view the results in the bottom pane.

   Caution If you use the SQL tab described in the following step, you will no longer be able to edit your query with the Design tab.

10. If you are an advanced user and would like to edit the query manually, click the SQL tab.
11. Do one of the following:
    • Click Save Query and complete the fields in the Save Query dialog box as required.
      • If you want to set an expiration date for your query, select an expiration date from the Expires menu. The system automatically deletes queries on their expiration date.

        Important When you choose the Save Results option described below, the system saves all records in the system that meet the criteria defined in your query, even if you used the Top Count option to specify that the system display only a limited number of records in the interface. Therefore, saving the results may take several minutes if your query has retrieved numerous records.

    • Click Save Results.
12. If required, use the following export option:
    • Export Results – Exports the results of a query in tab delimited or CSV format so that it can be viewed or opened by applications such as Microsoft Excel.

Result

Your query is saved and available for future use.

Additional Information – Advanced Users

If you are an advanced user with a solid understanding of SQL as well as knowledge of the PACS database architecture, you can edit SQL code within PACS.Query on the SQL tab in the query design area.

For examples of SQL code to use as a guide when building your own queries manually, see Appendix – Examples of SQL Query Code.

Additional Information – Conditions

For an overview of conditions that you can choose when designing your query, see the Query Design Area section of .

The Condition Editor dialog box can contain:

• A minimum value field.
• A maximum value field.
• A New in Value field, which you can use to define a set of values to use with the IN or NOT IN operators.
• A bottom pane with specific values available for selection as shown below. The bottom pane is not displayed for all conditions.

Additional Information – Joins

You can design queries using two types of joins: an inner join, and a left outer join. By default, all joins are inner joins. Therefore, if you want to user a left outer join, you must select Left Outer Joins and complete the relevant fields.

An inner join is inclusive. It is a join that returns records only when the join condition is met.

A left outer join is exclusive. It is a join that returns all records from the left-hand table regardless of whether the condition is met, and it returns records from the right table only when the condition is met.

See Also

• For information about using the main PACS.Query window, see PACS.Query Interface.

Additional Information – Year Selections

When you select more than one of the year fields sup_tax_yr, prop_val_yr, owner_tax_yr, tax_yr, sup_yr, and exmpt_tax_yr, we recommend that you do the following:

• Specify a condition for only one of the year fields.
• Select None for the Year toolbar option.

The query featured in this example procedure is designed to retrieve owner name and address records for all real and mobile home property for 2007.

We recommend that users with a basic level of SQL knowledge select fields from within a category when creating queries.

1. Select the following fields:
   • prop_id (table: property)
   • prop_type_cd (table: property)
   • prop_val_yr (table: property_profile)
   • addr_line1 (table: Owner Address)
   • addr_line2 (table: Owner Address)
   • addr_state (table: Owner Address)
   • addr_zip (table: Owner Address)
2. Clear the Select check box for prop_val_yr so that this field is not displayed in the query results.

   The fields are displayed in the query design.

   

3. Leave the Function for each field set to None.
4. For the prop_type_cd field, click the Add Condition button. Then select the operator IN, and select the values MH and R. so that the results include both real and mobile home properties.
5. For the prop_val_yr field, click the Add Condition button. Then select the operator IN, and select 2007.
6. If you want to check that the structure and design of the query is correct, click Verify SQL.

   The SQL code is displayed on the SQL tab.

   

7. Click Execute SQL to generate results for the query and view the results in the bottom pane.

   

8. Click Save Results.

See Also

For information about using the options in the PACS.Query interface, see PACS.Query Interface.

For information about the steps involved in creating a query, see Creating a Query.

src =

Purpose

Use this procedure to export query results by creating a file at a specified path in txt, csv, or xls format so that you can view the file using other applications.

Procedure

1. Click Export Results.

   

2. Select the output type.
3. To include table headers for your results, select Include Headers.
4. In the Path field, specify the file path under which to save the results.
5. Click Save.

Purpose

Use this procedure to run a query that you saved previously.

Procedure

1. Click My Queries to access your list of saved queries.
2. Click the query you wish to run.
3. To check that the structure and design of the query is correct, click Verify SQL.
4. Click Execute SQL.

Result

The query results are updated with the most recent data available in the system.

Purpose

Use this procedure to delete a query from your My Queries band.

Procedure

1. Click My Queries.
2. Select the query to be deleted, and then click Delete Query.
3. If you want to save the query but delete the results, select Delete Results Only.
4. Click Yes.

Result

The query or query results are deleted from your My Queries band.

This section provides examples of commonly used SQL queries to serve as starting point for building SQL queries of your own manually in PACS.Query.

This query returns a list of properties that are listed in the database as being less than 100 percent complete. This in effect creates a work list of properties to assign to appraisers for field checks for the year 2006.

select distinct i.prop_id, i.prop_val_yr, i.percent_complete

from imprv i

inner join property_val pv with (nolock)

on pv.prop_id = i.prop_id

and pv.sup_num = i.sup_num

and pv.prop_val_yr = i.prop_val_yr

where pv.prop_val_yr = 2006

and (pv.prop_inactive_dt is null or pv.udi_parent = 'T')

and i.percent_complete < 100.00