Posts From November, 2010

Oracle SQL - check for no table, no rows

I was recently tasked with an interesting issue.  The report that I created connected to a database table that would be refreshed regularly.  During the refresh process (sometimes taking minutes, sometimes longer), the report would fail to show any data, since the table was empty at that exact moment.  I needed a means to determine if the report had no rows, then I would display a message that the data was being updated.  The report was then to be migrated to another database, where the table didn't even exist yet.  In this case, I wanted to also display the same wait message.  Oracle doesn't have an obvious method to determine if a table exists.  Attempting to run Oracle SQL against a non-existent table results in an Oracle 904 error. 

I figured out that this code will do the trick, whether the table exists or not and whether the table has any rows in it or not.  It returns a binary 1 if the table doesn't exist or doesn't have any rows in it, otherwise a zero is returned.

Select
   CASE WHEN (select num_rows from all_tables where table_name = '<your table name>') IS NULL
   THEN 1
   ELSE 0
   END
from dual

 

Gravatar

Waterfall graph in Excel and Tableau

Lots of people are fond of creating a waterfall graph, but haven't figured out how to do it.

Here's the method that I've found to be simple and effective.  The basics are to first create four series:

A Base series for setting the beginning and ending balances. (blue)
An 'UP' or positive influence to the balance - (green)
A 'DOWN' or negative influence. -- (red)
A series that sets the bottom of the element in the UP or down.  This series is formatted with the border and area set to 'None'.

That's it.  The tricky part is the calculation to determine the number to use in the bottom series.  For the first element, it is simply the beginning balance less the down amount.  Subsequent columns are the prior bottom plus the prior up amount less the current column down amount.

Because I really like Tableau for graphics, I also used it to create the waterfall chart.  Download the Tableau workbook to understand how that was done.

Download Sample Excel Workbook Download Sample Tableau Workbook
Sample Excel Waterfall Chart:  Sample Tableau Waterfall Chart:
Gravatar

Two versions of Tableau on Right mouse

It seems that my Nov. 3, 2010 post on the Tableau forums is no longer part of the current displayed activity, so I've repeated it here for more lasting content.

Thought I'd share a registry hack that made my life easier after performing an install of a new Tableau release. When I have two versions on the desktop, then use Windows Explorer to view a directory, I can click on the right mouse to choose which program to open a Tableau file. Standard stuff, except when there are two or more versions of Tableau installed, since each has the same identity on the right mouse - 'Tableau'. To fix, I do a registry change. If you aren't comfortable with editing the registry, don't read any further. I won't take responsibility for messing up someone's registry.

Under HKEY_CURRENT_USER\Software\Microsoft\Windows\ShellNoRoam\MUICache you will find entries for Tableau. (I search for Tableau at this point). The Name column has the path name for the program, so it includes the version number. Hit edit and change the Data element to read something like 'Tableau 6', or whatever aligns with the version. Close regedit, check out the change to your right mouse selections. Voila!

Here's the regedit item for Vista:

HKEY_USERS\S-1-5-21-641386121-2680866733-628560399-1000\Software\Classes\Local Settings\Software\Microsoft\Windows\Shell\MuiCache

Hint: Control-F to bring up Find dialog box, enter Tableau, select just the Data, then press F3 to repeat until you get to your location, which may have a different hash code than mine after the HKEY_USERS. As before, look at the name column to figure out which exe is being used, then hit enter to change the Data.

Gravatar
Copyright© Brad Earle
Site Map | Printable View | © 2008 - 2021 KB Earle Associates LLC | |