pink cylinders  

More Than Four

...there's an axis for that.

Avoiding artificial inflation in SUM on a denormalized column value

by colin 28. August 2012 22:37

Say you have a data model that looks like this:

In other words, time entries can be tagged against multiple tasks. (I know this seems wrong, but trust me, it made sense in the context of the system.)

The problem we had was that the complicated query behind the report of "total hours by task" was counting the hours twice or more, for any entry tagged to more than one task.

select T.NAME, sum(L.HOURS) 
 from time_x_task X 
  join timelog L on L.ID=X.TIME_ID
  join task T on T.ID=X.TASK_ID
  group by T.NAME;

After thinking about how I could alter the query so that each timelog.HOURS was included only once in the SUM(), it occurred to me that it might be better to just distribute the hours across the tasks:

select L.ID, T.NAME, L.HOURS, L2.cnt, (L.HOURS/L2.CNT) as ACTUAL_HRS
 from time_x_task X
 join task T on T.ID=X.TASK_ID
 join timelog L on L.ID=X.TIME_ID
 join (select count(1) as CNT, TIME_ID 
         from time_x_task 
         group by TIME_ID) L2 on L2.TIME_ID=L.ID;

Now we SUM(ACTUAL_HRS) instead of sum(HOURS).

OK, this is a contrived example, but the point I'm trying to make is that sometimes you have to throw away the original question and think about what you're really trying to achieve. 

A light-weight SQL editor plugin for Eclipse IDE

by colin 24. August 2012 16:22

Eclipse is not my primary IDE, but I do find it very useful for editing ANT project definitions, especially the outline view.

I use generic projects in Eclipse Juno 4.2 to manage collections of DDL scripts, but I did miss SQL syntax highlighting. Well, no more! 

I found this plugin on the Internet:

Just download the jar and put it in the eclipse/plugins folder, then re-start Eclipse. It works great:

Long columns wrapping in the SQL Result Grid

by colin 19. August 2012 18:23

Have you ever experienced a moment when you find that an application you've used for years has a feature that, if only you'd known about it, would have saved you time over and over again? It's a mixed feeling... one one hand, it's "Yay, this software is awesome!" and the other it's "Doh! I'm an idiot!". 

Well, PL/SQL Developer just handed me another of those moments. 

In the result grid of the SQL Query window, you can resize any row by grabbing the separator line in the margin and pulling down. I think I knew that... but what didn't realize is that any column with a long string will then wrap into the extra space, like this:

I think that is pretty cool. And it's been in the product since at least version 8 (which is what I've used for the last few years).

header bg