Areas of Interest, as counted by my cat

Month: August 2012

Avoiding artificial inflation in SUM on a denormalized column value

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.

[code:c#]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; [/code]

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:

[code:c#]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;[/code]

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

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: http://blog.debertshaeuser.com/index.php?page=eclipse-sql-editor.

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

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).

© 2024 More Than Four

Theme by Anders NorenUp ↑