Cool SQL...

Here are some cool SQL tricks that I have learned...

Recently I had to order some some data but be case insensitive. I did it in Java using a Comparable, but this sql can do it also:

select *
from field_units
ORDER BY Lower(unit_metric_name)

This can find duplicate rows in a table:

select cust_id, site, count(*)
from alliance_customer_sites
group by cust_id, site
having count(*) > 1

This will, in Oracle, find all the tables in a schema:

select table_name, num_rows counter
from dba_tables
where owner = 'SCHEMA_NAME'
order by table_name;

This does the same in DB2 land:

select (SUBSTR(TBNAME, 1,30)) as Table, COLNO, (SUBSTR(NAME, 1,30)) as Column, COLTYPE, LENGTH, SCALE, DEFAULT, NULLS
from SysIBM.SysColumns
WHERE TBCreator = 'Z1Z10001$'

Here is a basic loop in Oracle:

BEGIN
--must have projects table loaded first...
--obviously you can join tables from different databases. Cool!
DELETE FROM project_commodities;
DECLARE
CURSOR c1
IS
select pc.proj_id, pc.type, ct.commodity_id
from project_commodities@z1sh pc, Commodity_types ct
where pc.TYPE = ct.COMMODITY_TYPE
order by proj_id ;
BEGIN
FOR x IN c1
LOOP
INSERT INTO project_commodities VALUES (x.proj_id, x.type, x.commodity_id) ;
END LOOP;
COMMIT;
END;
END;

This will find a stored procedure in Oracle:

this will find a stored proc.
select dbms_metadata.get_ddl('PROCEDURE','Stored_proc_name') FROM DUAL;

In DB2 land users need to add "WITH UR" to the end of all Select statements. In Hibernate, this is quite difficult, until you understand the power of the HibernateInterceptor.

1. Extend  org.hibernate.EmptyInterceptor.
2. Override the onPrepareStatement() method
3. This method has a String argument which is what Hibernate thinks the final SQL should look like.  Just append " with ur" to the end of it and go.
4. Remember, ALL sql goes through this, so what I have done in the past is add some "if" logic and look for the word Insert, update, delete etc (StringUtils.containsIgnoreCase(arg0, "insert")).  Don't add "with ur" to this.

 Another tip in DB2 land.  Strings and Chars are a pain as are Doubles and BigDecimal stuff.  Extend org.hibernate.dialect.DB2390Dialect and in the constructor you can "register" types:

public DB2DialectExtension() {
        super();
        registerHibernateType(Types.CHAR, Hibernate.STRING.getName());
        registerHibernateType(Types.DECIMAL, Hibernate.DOUBLE.getName());
    }

Doing this will keep the model beans nice and simple.  And nobody uses Char's anymore anyway...
These last two are hooked up this way:

 
 
 
  
 
 
  
   cat.dds.med.utils.DB2DialectExtension
   false
   none
   org.hibernate.cache.EhCacheProvider
   true
   true
   jndi/hibernate/medSessionFactory
   true
  
 

1. Line 1 is defining the Interceptor.
2. Line 5-7 are hooking it up.
3. Line 10 is using the new Dialect.

Sweet!