ThatJeffSmith

SQL Developer Quick Tip: Filtering your Data Grids

Regardless of your role or responsibility, if you work in the database, you’re going to spend a lot of time looking at data. This is usually what will push someone from the command-line to a GUI – the spreadsheet look and feel for browsing tables or query results. Since you’re going to be spending so much time in these grids, let’s make sure you know the basics.

I know you’re not writing very many SELECT * queries, but some of your friends might be. If you want to filter out the data client side, then the grids can help with that. For this blog I’ll be using my iTunes data I showed you how to import in an earlier post.

Filtering Columns

We can hide columns in the data grid display. So you can query for everything up front and decide to trim out the white noise later. I really wish you’d write better SELECTs, but I have to personally admit to being guilty of this. So when I forget about the awesome Query Builder interface that lets me after-the-fact click into columns to be written in the SELECT, I can still remove columns from the results panel in the worksheet.

Right-mouse-click on an column header will give me this context menu

Column Header Context Menu

And choosing ‘Columns…’ gives me this

Hiding columns from a data set in SQL Developer

Hiding columns does two things for me. One, it makes the data easier to browser. And B, it affects what is sent to PDF, HTML, XLS, etc. when you do an export. So you could say the Grids are WYSIWYG.

Let’s test that. All I want in my PDF is the Song title, album name, and time.

I’m also going to re-order the columns (using drag and drop on the column headers) and sorting by song NAME (double-clicking on the column header.)

WYSIWYG Data Grids

Here is the PDF created

PDF created by SQL Developer using Export...

Ok, that’s half the battle. Now let’s look at filtering the DATA (or rows) we want shown in the grids.

Filtering Rows

Again, I can’t stress enough that we should be doing the bulk of our filtering SERVER side. This means writing good WHERE or HAVING clauses in our queries. Let the big beefy servers do that work. Less network traffic, less memory and CPU required on your machine, yada, yada, yada…

Ok, but if the data already happens to be in SQL Developer, let’s look at how to filter by values in a particular cell.

You can use the same column header right-mouse-click and choose ‘Filter Column’, or you can LEFT click on the column header. Either way you’ll get a filter popup that looks like this:

Filtering data in a column on value.

I can click into a value and it will auto-filter on that value. This is pretty trivial – MOST of the time. When is it not trivial? When you want to filter on a GUID, a really hard to type field, or when working with DATE. Being able to click into a date and filter on that can be easier than writing a TO_CHAR or TO_DATE in your WHERE clause filters. I say it’s worth learning, but until you do, feel free to use the SQL Developer filters :)

Instead of clicking, you can also enter any valid WHERE clause. You don’t have to enter the WHERE or the column name, you can just enter the qualifier.

Input and remove filters

1 – the Funnel icon appears in any column that’s being filtered
2 – open the filter, then click on ‘Remove’ to clear the filter

You can also mouse-right-click on the column headers again and choose ‘Remove All Filters.’ Note that this will only remove your filters and not bring back any columns that have been hidden.

If you have added a bunch of filters and you can’t quite remember what the filter exactly was on a particular column, you can simply mouse-over the funnel in the column header

This is all great, but I don’t even know where my data is!

That’s OK, the grids have a Search mechanism. It’s hot wired to Ctrl+F, the standard ‘Find’ shortcut. Just make sure the focus is on the grid, else you’ll launch a worksheet editor search/replace dialog.

Find and highlight values in a grid with Ctrl+F

You’ll notice that I checked the ‘Persist Highlight’ option. This means if I close SQL Developer and come back to this data in a few weeks, the highlighting will remain! You can add as many of these as you would like. They can be toggled on or off or removed permanently. I learned this particular ‘trick’ just a few weeks ago at one of our SQL Developer Dev Days workshops.

If you want to attend one of these events, the next one is being held in Denver in coordination with RMOUG. It’s free for anyone that can get there (the workshop, not the RMOUG event itself.)