  |
|
CoolQuery HelpContents
1. OverviewCoolQuery provides some very useful and powerful tools to the user, but with the ease of point-and-click management. What does CoolQuery do?
What is SQL? SQL is a language for querying databases. It's original name was Structured English Query Language, later shortened to Structured Query Language. Some pronounce it "sequel" and some just say "S-Q-L." Take your choice. CoolQuery provides a user interface for building SQL select statements and generating results that are both viewed online in an html table as well as written to a CSV (comma separated value) text file for download. Most Windows machines will have an association between a file with a .csv extension and the MS Excel program. If you open Windows Explorer and see a listing for a .csv file, you will notice that it is marked with the Excel icon. Double-click the file, and it will open in Excel. (Since a .csv file is in reality a plain text file, you can also open it from NotePad or any other text editor.) An SQL select statement as built by CoolQuery has three main parts: A fourth part which is optional is the "Order By" clause, which will tell the database to sort the results based on certain columns. This may not be of great value, since you can do ordering in Excel, but you may find it helpful to have the initial results in a particular order. Excel can only order by three columns at a time, so you may find it helpful to have some of the sorting done prior to working with the data in Excel. When you use CoolQuery to build a select query, you determine the "from" section of the SQL statement first by selecting the database object (view or table) you will query. Then you are provided with a list of columns that this object holds, and you select the columns that hold the information you want to include in your report; this is the Select portion of the SQL statement. Finally, you provide rules or filters. Here you say to the database: I don't want data for everyone in the view; just where the term is such and so, or where the state is such and so. In the end, CoolQuery will create an SQL statement based on what you have chosen, and this SQL statement is in a form that the database understands. Here's an example of an SQL statement: This will provide a list of term_code_key and state1 for any kind of student and any term, but only for students who do not have 'NY' in the state1 column. Not, perhaps, a very useful query, but it gives you an idea. SQL statements are not difficult to understand, as you see. [ Top of Page ]
2. Logging On to CoolQueryIn order to use CoolQuery, you must have connect privileges to the database, as well as be assigned to an appropriate role which in turn provides access to the database object/s which hold the data you will query. When you log on to the CoolQuery application, regardless of what privileges you have on the database, you will be granted only specific privileges provided to you by CoolQuery roles. If any of you are ever able to see other database objects (tables, views, etc.) other than those specifically provided by CoolQuery, then you have located a bug; please report it to the ITS Service Center (ITS.ServiceCenter@fredonia.edu)! The way this works: We have created ROLES on the database which have SELECT permissions to particular views, and not to anything else. You as users are assigned to these roles. When you log on to CoolQuery, it disregards any other permissions you may have on the database and looks only at the ROLES that are specific to the CoolQuery program. Your other permissions are not affected; they simply are not available to you through the CoolQuery interface. They remain for you via other ways of connecting to the database (through forms, etc.). There are a number of 'objects' in the database which can be accessed through CoolQuery. What you see is based on your own security roles. Your co-worker may have a different collection of objects available. This does not have any affect on how CoolQuery works; all the tools in the program remain identical. The CoolQuery roles have select permissions to these objects. "SELECT" permissions means that you can read information from the view. However, you cannot modify, delete or add data, nor can you delete the view. (This is a good thing!) These objects are created new daily. Therefore, when you access this data, it is always within 24 hours of being up-to-date. Note: The date and time of the last 'refresh' date is listed to the right of each view name. When you initially log on to CoolQuery, the program will do the following:
Also listed among your saved queries might be queries that you did not build, and that are not limited to the objects described above. These would be queries that a programmer may have built for you and provided for you to run. Generally, you will know about these, as they would have most likely been provided by a programmer as a result of your request. [ Top of Page ]
3. Help and CommentsThere are 'helps' built into CoolQuery to assist you in knowing more about the objects, columns and queries. Most of the time, you can click on the name of an object, a query or a column, and a new window will pop up which will display information about that object, query or column. When you create a saved query, you will have the opportunity to enter a description of that query. Use this feature to provide yourself with any information about the query that you want. Later, when you are looking at a list of your saved queries, click on the query name and you will see your description of the query. If you are creating saved queries that you wish to share with others who are in the same role as you (that is, who have select privileges on the same object you are building the query from), please write descriptions that will be meaningful not only to you, but to others as well. You can choose to share a saved query when you are saving it, by putting a check mark in the "Share" check box. You can also toggle sharing on/off for any of Your Saved Queries at any time by going to the Manage Queries section of CoolQuery. Single Quotes A database can hold many different types of data. Text, numbers and dates are common types. In order to distinguish between text and variables that may be pointers to different data values, text content must always be enclosed between single quotes. This is important for you when you build the "where" clause of your query. To assist in this, a "Quote Help" link is included on the screen where you create rules. Clicking on this link will provide a pop-up window that lists all of the columns that should NOT use single quotes.
Some users may report that they are able to ignore this rule and get the same results. The Oracle database is sometimes able to make conversions from one data-type to another implicitly, and when that is the case you may find that you get the same results with or without the single quotes. In another case, however, you may not get correct results, and you may not understand why. Neither will the programmers, initially, either, if you turn to them for help. Please follow correct conventions and always use single quotes for text strings! [ Top of Page ]
4. Saving Queries for Re-UseWhen I described the SQL statement, I indicated that you would be building rules to determine what sort of information a query returns. This takes place on the 'qwhere.asp' page. On this same page, you can select columns by which to order the result set, if you wish. Here is where you can also indicate to CoolQuery that this is a query you with to save for future use.
Obviously, it is not a requirement that you save every query you run. Sometimes you have a particular need for information that you want now, but are not likely to need over and over. There's not much point in saving such a query. Other times, however, you are going to build a query that provides some standard information that you need to have again and again, with the most recent data. This is where a saved query is helpful. Once you have a query doing just what you want, give it a name and describe it, and it will always be there for you to run. Just select it and click on "Run Query" and you will immediately have the results to view and download. Dynamic Queries Another powerful feature of CoolQuery is the ability to create dynamic queries. Instead of putting a value in the where clause, make the first character an ampersand and follow it with either the column name or a meaningful indicator of what it is looking for. Important! Use only alphanumeric or underscore characters (a-zA-Z0-9_) in dynamic variable names. "&Class Code" will not work. Instead, use &ClassCode or &Class_Code. Then, when you run the saved query, you will be prompted to provide some information before the query is run. As an example, &Term in the Value field would cause a form to pop up and you would be prompted to provide a Value at that time. Now you can select the term you want. This way, you do not need to create a saved query which is exactly the same as another except for a value change in columns. You can have as many dynamic values as you want in a saved query. Single Quotes and Dynamic Queries: If you are making a dynamic query, best practice is to put the dynamic variable in without the single quotes, like this: &LastName_LIKE even though you know it is a text field that requires single quotes. Then, when you are prompted for the value, include the single quotes, like this 'Ger%'. [ Top of Page ]
5. The RecordsetThe recordset is the results that are returned from the database based on the SQL query that you and CoolQuery have built together. On the "qview.asp" page you will see the data displayed, and you will also be able to download the results in a .csv file from a "Download File" link at the bottom of the page. There is no requirement that you download the file. If you wish only to "eyeball" the results on the web page, that's fine. Downloading Your Report CoolQuery incorporates your database username with a random number to create the filename. If you right-click on "Download File" you can then select "Save Target As..." and rename the file before it is copied to your comptuter. [ Top of Page ] About Cloning If you see a public query here that you like, don't count on it staying around for your continued use. Someone else owns it; they can toggle sharing on and off, rename the query or delete the query whenever they want. Either talk to the owner to see if it will be staying around awhile, or better, just clone it. When you click on "Clone a Public Query" you will be presented with a drop-down list of queries available for you to clone. Select the query you wish to clone and continue. You will be prompted to provide a new name. Then you are done; now you have a new query in the "Your Saved Queries" list, just as if you had created the query yourself. The query is saved along with its attributes except for one difference. It will be toggled to "Not Shared" when you first get it. You can manage the query and change that to Shared if you wish - though what's the point? This is a copy of a query that is already shared to public . . . or you'd not have been able to see or clone it. If the 'expert mode indicator' is set to 'N' you will have full edit capacity. If it is set to 'Y' you can toggle sharing, edit the description, rename or delete the query. One of the uses of cloning a query might just be to get a glance at the inner workings of someone else's query because you are trying to do something similar in a query you are building. Look, learn, and delete if you wish. There's no rule that says you have to keep cloned queries - or any other queries, for that matter - around. [ Top of Page ] What's this about "Expert Mode Indicator?" CoolQuery saves your query in several pieces spread over a few tables. It also saves the entire query in one table cell in just one of those tables. If you use the 'advanced mode' and choose to view your SQL statement at the same time you view the results of your query, you will have the opportunity to Edit your SQL statement in a text box. This will alter the entire query in the single table cell, but it does not map to the other tables the way the regular CoolQuery interface can do. When this happens, a flag is thrown to alert CoolQuery that a given query can no longer be edited via the CoolQuery graphical interface to the same degree when you wish to manage the query. You cannot change the select, order or where statements; you can still toggle sharing, edit the description, rename or delete the query. To edit the sql statement itself, you will use the sql edit box. [ Top of Page ] Manage Queries Managing your queries is quite easy and should be self-explanatory. Here you can perform the following:
[ Top of Page ] Date Format Dates are stored in the Oracle database in this format: DD-MON-YYYY, where the days and years are numeric, but the month is alpha. For example, "January 23, 2002" aka "01/23/02" or "1/23/2002" or many other possible formats, should be entered in the rules for the where clause as '23-JAN-2002'. [ Top of Page ] Using Banner Popsels Banner Popsels use criteria from outside the views available to Coolquery to identify a population selection. You can refer to and use these popsels in Coolquery to determine what population you gather information about. You do this when building the 'where' portion of the sql statement by specifying "PIDM_KEY IN POPSEL."
[ Top of Page ] Download Additional Help Files
[ Top of Page ]
|
CoolQuery
If you make an error by using quotes when you should not have done so, or neglecting to use them when you should have, simply use your back button and make the necessary correction. You may not be able to determine just what an error means, but do use your back button, use the "Quote Help" to check, and try again. Don't worry, you can't do any harm by making such a mistake!
When Coolquery sees the word "Popsel" following the "IN" operator, it replaces "Popsel" with a dynamic subquery that prompts you for information regarding the popsel.