Query Tips
| MISWEB Home Page |
Included here are some useful tips to keep in mind when querying the MISCOMP database. The Query Tips include tips that are valid when querying MISCOMP through any SQL, as well as details that apply specifically to the MISWEB query interface.
The following query tips are available:
SQL databases store and compare data in a case sensitive manner. You must query the database using the correct case for text fields. Typical SQL implemenations include conversion functions, such as Oracle's upper(), lower(), and initcap() functions. Using these functions on the column, you can insure that your qualifier will be compared in the same case, e.g. where lower(name) = 'johnson'. Unfortunately, when you use a function on columnar data, this disables the use of indeces when comparing that column. Without the use of the index, SQL databases must traverse through the entire table comparing all rows. This can be tedious with large tables. To avoid this, it is recommended that users learn the case of their data and query accordingly.
The MISCOMP database stores most text fields in all upper case. The only text fields which allow mixed case are description or comment fields. This is typical in Oracle databases.
The MISWEB custom query builder allows you to specify a case conversion function when performing queries. Note that in order to ensure that queries are performed as quickly as possible, this conversion function is applied to your input data, NOT to the database column value. Thus, you must still know the case of your data. You just don't have to type your input in that case. This is useful if your used to Unix systems and are not used to typing with the CAPS LOCK ON.
Dates are stored in Oracle as seven bytes, which includes the century, year, month, day, hour, minute, and second. But, when querying and displaying dates Oracle's default format is DD-MON-YY, where DD is a two digit number representing the date, MON is the first three characters of the month, and YY is a two digit number for the year. Note, 01-JAN-01 is really January 01, 1901 (not 2001).
You can always reformat the date, however, using the to_date and to_char functions. The to_date function accepts a character string, which contains a valid date but one which is not in the Oracle default format, and converts it to a date. The first argument is the character string containing the date, and the second argument is the current format of this character string. When invoked, to_date converts the string into a valid date and returns the date in Oracle's default format. Some examples are included here:
The to_char function converts a date value to a character string. This function is useful when you want to display the date in a format other than the Oracle default. It accepts two arguments, the date value and the format for the resulting character string. For example:
The MISWEB custom query builder allows you to specify a to_date function when querying a database column. When you specify this function, MISWEB will convert your input from either the standard Oracle date format, DD-MON-YY, or from the more common date format, MM/DD/YY, so that the SQL query will properly compare values. MISWEB also handles the full four digit year if specified. MISWEB's to_date feature does not allow you to specify a date format as it only supports these two date formats. OK, so MISWEB's not perfect, when using to_date, you must include the full two digits such as 01/01/97.
When querying the Oracle database, the wildcard is the percent sign, %. This wildcard, combined with like operator must be used for pattern matching. For example:
In order to ease the burden on the user, the MISWEB utility allows the web page developer to automate the prepending or appending of the % wildcard. Thus, many of the predefined queries include appropriate wildcards. For example, when you query by the Property Number, the predefined web pages automatically prepend the % wildcard, which avoids you having to know if there is a leading zero in the property number you are querying. Note, however, that the MISWEB custom query builder does NOT automatically prepend or append any wildcards. You must include these in your input where needed.
| For help contact miscomp@fnal.gov |