Funny SQL I Have Seen Lately

I’ve run across a few strange SQL statements lately, and while the mistakes they make are not the most impactful, I thought I’d share how I think they could be better written.

Unnecessary use of a Common Table Expression

This query isn’t really wrong, it just doesn’t make much sense. Here it is as I discovered it:

with sortsnap as
(
select
    float(commit_sql_stmts) as com_stmt,
    float(rollback_sql_stmts) as roll_stmt,
    float(total_sorts) as t_sorts,
    float(sort_overflows) as s_over,
    float(sort_heap_allocated) as s_heap_alloc,
    float(total_sort_time) as t_sort_time,
    float(active_sorts) as a_sorts
from table (SNAPSHOT_DATABASE('',-1)) as SNAPSHOT_DATABASE
)

select 
bigint(s_heap_alloc)  ,  
bigint(t_sorts)  ,  
bigint(t_sort_time)  ,  
bigint(s_over) ,  
bigint(a_sorts) ,
bigint(com_stmt)  ,  
bigint(roll_stmt)  ,  
bigint(com_stmt+roll_stmt),
bigint((t_sorts+1)/(com_stmt+roll_stmt+1)),
bigint((s_over+1)/(t_sorts+1))
from sortsnap;

In my opinion, the Common Table Expression used here is completely unneeded – we can easily perform all the work in a single query. It is also querying the table function SNAPSHOT_DATABASE, which is no longer available starting in DB2 10.5. I’m not going to change that part, as I don’t need to for the use I have for this statement. I also find the aliases used for the columns unneeded and confusing. I also don’t like the form used for verifying division by zero is not attempted.

Here’s how I’ve re-written that SQL:

select     bigint(sort_heap_allocated)
    , bigint(total_sorts)  
    , bigint(total_sort_time)  
    , bigint(sort_overflows) 
    , bigint(active_sorts) 
    , bigint(commit_sql_stmts)  
    , bigint(rollback_sql_stmts)  
    , bigint(rollback_sql_stmts+rollback_sql_stmts)
    , case when commit_sql_stmts + rollback_sql_stmts > 0 
        then decimal((float(total_sorts)/(float(commit_sql_stmts)+float(rollback_sql_stmts))),21,2)
        else 0
      end
    , case when total_sorts > 0 
        then decimal((float(sort_overflows)/float(total_sorts)),21,2)
        else 0
      end
from table (SNAPSHOT_DATABASE('',-1)) as SNAPSHOT_DATABASE
;

I added case statements to verify that I’m never dividing by 0. There are a number of ways to do this – the previous one added one to each side to accomplish the same thing – a trick I’m not fond of. I also changed the last two returns to decimals, since that makes more sense than integers for where this data is going and what it is saying.

Creating Views Instead of using a Common Table Expression

While the last one used a Common Table Expression where it was absolutely not needed, this example instead created and dropped views for apparently no purpose:

create view DB2_MON_APPL as select * from table(snapshot_appl('',-1)) as sntable;
create view DB2_MON_APPL_INFO as select * from table(snapshot_appl_info('',-1)) as sntable;

select  ai.appl_id,
    ai.appl_name, 
    ai.appl_status,
    ap.locks_held,
    ap.lock_wait_time
from    db2_mon_appl ap, db2_mon_appl_info ai
where   ap.agent_id = ai.agent_id
;
drop view DB2_MON_APPL;
drop view DB2_MON_APPL_INFO;

Perhaps the person writing this one didn’t know you could join two table functions? I’m perplexed. I re-wrote this one as:

select ai.appl_id
    , ai.appl_name
    , ai.appl_status
    , ap.locks_held
    , ap.lock_wait_time

from    table(snapshot_appl('',-1)) as ap
  join table(snapshot_appl_info('',-1)) ai on ap.agent_id = ai.agent_id;

I’d love to hear what completely off-the wall SQL you’ve found that just defies logic – please share in the comments!