Queries are a powerful tool to generate insights and help you understand your database. The recent TAI Ad-Hoc Query Webinar, led by Mindy Epstein, Senior Consultant, ALMI, ARA, explained how you can make the most of the ad-hoc query function on the .NET platform. Mindy offered an overview of the function, including how to access, navigate, and design queries, select output options and start learning SQL structure.
Read the highlights here and log in to TAI’s Help Desk in the Knowledge Base for access to the full webinar recording.
How do I use the Ad-Hoc Query function?
The ad-hoc query function is available in the .NET version of TAI. The function can be found under the Utilities menu. From there, you’ll be taken to the SQL Query screen. Here you’ll be able to directly input SQL statements.
The default settings will retrieve a grid output of the top 50 results. This prevents you from pulling queries with data that is too large to be displayed. Try not to query against a table with 1 million records – they won’t show up! If you don’t want the top 50, check the box that reads “Don’t Add ‘Top 50’ Limit.” You will also need to remove the words “Top 50” if they appear in your SQL statement. The result count can also be changed to a customized number.
What kind of SQL Queries can I include?
If you’re familiar with SQL and the information you’re looking for, you can start typing in the SQL Query Box. Queries can be written to pull information from a single table or can pull from multiple tables. A query requires a ‘Select” and ‘From’ statement. A ‘Group by’ statement is optional.
You can also save queries to reuse them later or run them during different time periods. If you want to delete a query, remember to double check that it is your query. The queries are saved in a shared location, so make sure you are not deleting someone else’s queries and work by accident. Once deleted, they cannot be recovered.
I don’t know how to write SQL Queries – what can I do?
Sample SQL Queries can be found in the SQL Help PDF document. It can help you narrow down what you want to query and understand what information may be helpful based on the output you want. The document includes a long list of sample SQL Queries, operations, and functions and is a good place to start.
Review this table to understand how SQL statements work:
If you’re not sure what you want to query or what tables you might need, the Design Query button can help you create a custom SQL Query. Once you click Design Query, you’ll be presented with Database options that include a list of tables to choose from.
From there, you can complete Criteria Selection from all the available fields in the table that you would like to return – whether it’s greater than a certain value, equal to, or excluding certain criteria, choose the parameters and click Add.
Then you’ll be able to determine the Sort Selection to determine how the information will be organized. You can add multiple criteria and sort functions by changing your selection and clicking Add. You’ll also be able to select if it is an ‘And’ statement or an ‘Or’ statement.
What are my output options?
The default is to send to the grid, which will appear on the screen below the query. You are also able to select CSV, Excel, Extract, or Report output options. Depending on the output you choose, you can opt to include headings. For example, for an Excel output, it would make sense to include headings.
You can also change the name that will be included in the file name. This will impact where it is located in the reports folder where it will be saved. A message will appear that indicates where the output file is stored, or you can click the ‘View Report’ link below the query box to download the document or the ‘View Extract’ link to see the extract online.
The concept of querying databases is universal, so even if you’re not a .NET user, there’s lots of useful information to learn from the webinar.
Visit the Knowledge Base on TAI’s Help Desk to access the full recording of this and other TAI webinars. Subscribe to the TAI blog for more resources to help you make the most of your reinsurance administration system.