pink cylinders  

More Than Four

...there's an axis for that.

Testing using SQL and common table expressions

by colin 3. September 2016 15:07

Sometimes I forget how to do simple things, like splitting a string into sections based on a delimiter.

Here's a nice way to test your code on a range of input values, and see the interim results, in one hit:

with 
myobject as (
   -- put in a range of test values:
   select 'dbo.MyFunction' as NAME
   union
   select 'setup.ins_Contact'
   union
   select 'MyOtherFunction'
),
more2 as (
   -- calculate interesting attributes:
   select
      NAME,
      len( NAME )            as LENGTH_,
      charindex( '.', NAME ) as POSITION
   from myobject
)
-- perform the test:
select 
   NAME, POSITION, LENGTH_,
   case when POSITION=0 then 'dbo'
                        else substring( NAME, 1, POSITION-1 ) 
                        end as SCHEMA_,
   substring( NAME, POSITION+1 , (LENGTH_ - POSITION ) ) as OBJECT_
from more2;
;

Implementing a Proper() case function in PL/SQL

by colin 2. October 2013 23:36

I found that I needed to convert some raw column names (i.e. THIS_IS_MY_COLUMN) into a human-readable "Proper" or "Title" case form, e.g. This Is My Column.

Scouring the web, I didn't find out I could steal, so here's my implementation:

   function Proper(
p_string varchar2
) return varchar2
is
l_string varchar2(200);
l_proper varchar2(200);
begin
l_proper := '';
-- change any underscores to spaces:
l_string := replace(lower(p_string), '_',' ' );

for i in 1..length(l_string)
loop
-- obviously the first character is UPPER:
if i =1 then
l_proper := l_proper || upper( substr( l_string, i, 1 ) );
else
-- if the character is preceded by a space, UPPER it:
if substr( l_string, i-1, 1 ) = ' ' then
l_proper := l_proper || upper( substr( l_string, i, 1 ) );
else
l_proper := l_proper || substr( l_string, i, 1 );
end if;
end if;
end loop;
return l_proper;
   end;
 

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. 

header bg