Today, I’m writing about the WHERE clause available under the ‘Advanced Search’ menu of most applications. Users often run straight to their Maximo administrator for help writing these queries because they know that writing them requires knowledge of SQL (pronounced “sequel”, and short for Structured Query Language). Although you might need the help of a true SQL expert for something really tricky, it doesn’t take too much to learn how to write simple SQL queries and do some of this yourself.
For easy, step-by-step lessons that will teach you introductory SQL, check out this website - http://www.sqlcourse.com/. This was actually the first place I learned basic SQL 13+ years ago. Amazingly, it’s still there and doesn’t look like it’s changed much. The site does a good job of explaining you the concepts of SQL while giving you well-paced exercises along the way. The whole thing should take you less than a couple hours, and you’ll have learned a valuable skill when you’re done. Keep in mind that this only covers the basics. It takes years and lots of practice to learn how to craft complex SQL queries. My next blog entry will include a few more complex queries that you might find useful (I’ll include the full SQL code for you to copy, if you’d like).
Then don’t hesitate to jump in and play around. Maximo won’t let you run any SQL that could make changes to the database (that’s called “SQL Injection”, and Maximo guards carefully against it because it can be used maliciously), so the worst thing you could do is write a query that returns an error or doesn’t give you what you want.
Maximo also uses SQL in a few other places, such as escalations, conditional expressions, ad-hoc reporting, and database configuration. But those applications are beyond the scope of today’s blog.
To wrap up, here are a few tips for writing WHERE clauses.
Filters/WHERE Clauses can be saved so that they can be selected the next time you or other users open an application. Inn other words, you don't have to write them again. They can also be used for result sets on the start center, and they have tie-ins to reports as well. All that is beyond the scope of this post, but feel free to reach out to me and I’d be glad to walk you through it.
Most applications have default WHERE clauses. These are the filters that are applied automatically when you open an application. For example, when you open Work Order Tracking, the WHERE clause is already set to display only work orders for your site and to filter out closed or canceled work orders. This default WHERE clause is usually wiped out if you select “All Records”. If you intend to build a custom filter, you probably want to include the default SQL in your new WHERE clause, so study it carefully. That means appending your custom SQL to the end of the default WHERE clause instead of replacing it. If you don’t know what it means, it’s probably best to assume that it should be part of your new SQL.
Maximo can get your SQL started for you. Any filters that you apply using the user interface will be added to the WHERE clause, and then you can add your custom SQL to it when you open the editor dialog.
You might need to include a field in your query, but not know the actual database column name for it. To find the column name, click your mouse inside the field (or checkbox, etc.) and type “ALT + I” on your keyboard (“I” is for “Information”). Maximo will display a dialog box with the column name.
Sometimes you'll want to include something in your SQL that might be different each time you run it. For example, the current date. To avoid changing the date each time you apply a filter, you can use variables. The most common variables are :&USERNAME& (for the current logged in user), :&PERSONID& (for the current logged in person), and :&DATE& or :&DATETIME& (for the current date or date/time, respectively). One important word of warning though – when you save the query, these values will be overwritten by the actual current value. So although you might type “personresponsbile = :&PERSONID&”, Maximo will translate the variable and save it as “personresponsbible = ‘JSMITH’”. The only way to fix this is to edit the query after you have saved it and re-insert the variable. To do this, open the ‘Save Query’ drop-down menu and select “View/Manage Queries”. Restore it to the way you wrote it in the first place: using the variable(s).