Using SQL to Create Custom Datatables

TCP HOME / Using SQL To Create Custom Datatables

Using SQL To Create Custom Datatables

SQL (I’ve heard it pronounced “Sequel” and “Ess-Cue-Ell”) stands for Structured Query Language, and is the method by which you retrieve data from your SQL-Server datatables. This document will give you specific tips for creating SQL scripts for TCP to generate reports; for much more detailed information, consult the web, searching for “SQL tutorial” as a start.

Please note that TCP currently supports only the SQL SELECT command, for reasons that become obvious if you start learning about the power of SQL (in particular, its power to completely delete all your data with one simple command!) SELECT’s only function is to retrieve data and place it in table form for you to view. You normally do not edit this data; editing data is done via a tabbed dialog interface. So using SELECT in this way is thus pretty safe for all users; the only thing you need to do is make sure your users are authorized to view the data.

At its most basic, SELECT could be:

SELECT * FROM GEN

Although I’m putting these commands in CAPS, SQL is not case-sensitive. (Tip: Copy and paste these commands into your Search | Custom Database SQL Script text box and press the Run button.) The above command will select all the columns and all the rows in your Gen datatable, and generate a new temporary table for you to view. The “*” is just a way of saying “all columns.” You can specify particular colums as well:

SELECT CASENO, LAST_NAME, FIRST_NAME FROM GEN

This will return all the rows from the three above columns from the Gen datatable. This is a good idea when you just want a particular set of data that you might copy, for example, into a spreadsheet for a report. Note that the Gen datatable houses most of the data on cases that you will want to find, such as open dates, close dates, assigned attorneys, court dates, and so on. To find all the columns in Gen, try a Custom Database search on Gen and you can scroll through them (columns are vertical, rows are horizontal). Note that you can click on a column heading to sort the table up or down to get a feel for the kind of data you want to retrieve. Note that Caseno is the unique number given to each of your cases. It is always a good idea to include Caseno in your searches in case you need to adjust your data to make it more accurate.

But just returning all rows (or records) of a datatable is not usually very helpful. So we will add the WHERE clause. WHERE is used to select records where a particular column or column matches or is like a search criteria. You use “=”, “<=”, “>=”, or “LIKE” in the majority of situations. It is beyond the scope of this little tutorial to demonstrate the differences, but if you experiment, you will pick up the nomenclature pretty quickly. So here I am querying for all cases opened in the year 2011:

SELECT * FROM GEN WHERE OPEN_DATE >= ‘01/01/2011’ AND OPEN_DATE <= ‘12/31/2011’

A couple things to note: First, you can use the AND clause to specify more than one WHERE. Second, you need to put your searched-for item inside single (‘) quotes, not double quotes, otherwise you will generate an error. Finally, make sure you have a valid column name for your search. You can also specify the datatable name explicitly by using a period (.), e.g. “GEN.OPEN_DATE”; actually, you have to do this if you are concatenating two datatables (another topic beyond this tutorial!)

SQL SELECT can also generate tables of incomplete data entries for you to fix prior to generating reports. Suppose you have closed files from prior court dates with close dates and close dispo codes on the Admin tab. Now you want to generate a report of all cases closed in, say 2011. You might try:

SELECT CASENO, DISPO_DATE, DISPO_CODE FROM GEN WHERE DISPO_DATE >= ‘01/01/2011’ AND DISPO_DATE <= ‘12/31/2011’

If you click on the Dispo_Code column, you can sort by disposition, but you might find some cases with blank disposition codes, which makes for incomplete data. You might want to try instead:

SELECT CASENO, DISPO_DATE, DISPO_CODE FROM GEN WHERE DISPO_DATE >= ‘01/01/2011’ AND DISPO_DATE <= ‘12/31/2011’ AND DISPO_CODE = ‘’

If that’s hard to read, I’ve used two single quote marks after “DISPO_CODE =” to tell SQL Server I want the blanks. This will give you a smaller table of just those cases you need to fix before running your report.

Users will undoubtedly come up with all kinds of queries of their data to meet their business needs. If you run across a query you think would be useful to the group, please email it to the TCP Helpdesk. We will publish a table of “Useful SQL Queries” after we have generated some responses from users. Also contact the Helpdesk if you need specific help in structuring a query, and we will get you set up.

This document is called SQLScriptTutorial.docx, and was last modified April 30, 2012.

1 | Page