Converting Number to Words Using SQL - Once Again

Hi

This post is related to
http://blog.badvised.com/2009/12/converting-number-to-words-using-sql.html

For some reason, one can not post a comment that is too long, so I have opened this post in order to share with you an alternative solution that was sent to me by Sriram Lakshminarasimhan. His solution does not require additional tables as I used in my implementation.
Sriram, thank you for sharing!

WITH CTETAB(WORKSTR,STR,COUNT) AS                             
  (SELECT CHAR('000000000001234567'),CHAR('XXX'),0            
     FROM SYSIBM.SYSDUMMY1                                    
    UNION ALL                                                 
     SELECT WORKSTR,SUBSTR(WORKSTR,1+(3*COUNT),3),COUNT+1     
       FROM CTETAB WHERE COUNT <  6                           
   )                                                          
    SELECT                                                    
    CASE SUBSTR(STR,1,1)                                      
        WHEN '0'  THEN '     '                                
        WHEN '1' THEN 'ONE   HUNDRED AND '                    
        WHEN '2' THEN 'TWO   HUNDRED AND '                    
        WHEN '3' THEN 'THREE HUNDRED AND '                    
        WHEN '4' THEN 'FOUR  HUNDRED AND '                    
        WHEN '5' THEN 'FIVE  HUNDRED AND '                    
        WHEN '6' THEN 'SIX   HUNDRED AND '                    
        WHEN '7' THEN 'SEVEN HUNDRED AND '                    
        WHEN '8' THEN 'EIGHT HUNDRED AND '                    
        WHEN '9' THEN 'NINE  HUNDRED AND '                    
    END     ,                                                 
    CASE SUBSTR(STR,2,1)                                      
        WHEN '0'  THEN                                        
              CASE SUBSTR(STR,3,1)                            
                      WHEN '0' THEN ' '                             
                      WHEN '1' THEN ' ONE '                         
                      WHEN '2' THEN ' TWO '                         
                      WHEN '3' THEN ' THREE '                       
                      WHEN '4' THEN ' FOUR '                        
                      WHEN '5' THEN ' FIVE '                        
                      WHEN '6' THEN ' SIX '                         
                      WHEN '7' THEN ' SEVEN '                       
                      WHEN '8' THEN ' EIGHT '                       
                      WHEN '9' THEN ' NINE  '                       
                    END                                             
              WHEN '1' THEN                                         
                    CASE SUBSTR(STR,3,1)                            
                      WHEN '0' THEN ' TEN '                         
                      WHEN '1' THEN ' ELEVEN '                      
                      WHEN '2' THEN ' TWELVE '                      
                      WHEN '3' THEN ' THIRTEEN '                    
                      WHEN '4' THEN ' FOURTEEN '                    
                      WHEN '5' THEN ' FIFTEEN '                     
                      WHEN '6' THEN ' SIXTEEN '                     
                      WHEN '7' THEN ' SEVENTEEN '                   
                      WHEN '8' THEN ' EIGHTEEN '                    
                      WHEN '9' THEN ' NINETEEN '                    
                    END                                             
              WHEN '2' THEN  ' TWENTY '                             
              WHEN '3' THEN  ' THIRTY '                             
              WHEN '4' THEN  ' FORTY '                            
              WHEN '5' THEN  ' FIFTY '                            
              WHEN '6' THEN  ' SIXTY '                            
              WHEN '7' THEN  ' SEVENTY '                          
              WHEN '8' THEN  ' EIGHTY '                           
              WHEN '9' THEN  ' NINETY '                           
          END ,                                                   
          CASE SUBSTR(STR,2,1)                                    
            WHEN '0' THEN '     '                                 
            WHEN '1' THEN '     '                                 
            ELSE                                                  
             CASE SUBSTR(STR,3,1)                                 
                 WHEN '0' THEN ' '                                
                 WHEN '1' THEN ' ONE '                            
                 WHEN '2' THEN ' TWO '                            
                 WHEN '3' THEN ' THREE '                          
                 WHEN '4' THEN ' FOUR '                           
                 WHEN '5' THEN ' FIVE '                           
                 WHEN '6' THEN ' SIX '                            
                 WHEN '7' THEN ' SEVEN '                          
                 WHEN '8' THEN ' EIGHT '                          
                 WHEN '9' THEN ' NINE  '                          
             END                                                  
          END ,                                                   
          CASE COUNT                                              
            WHEN  1  THEN                                         
                CASE STR                                           
                  WHEN '000' THEN '   '                            
                  ELSE 'ZILLION ,    '                             
                END                                                
             WHEN  2  THEN                                         
                CASE STR                                           
                  WHEN '000' THEN '   '                            
                  ELSE 'TRILLION ,   '                             
                END                                                
             WHEN  3  THEN                                         
                CASE STR                                           
                  WHEN '000' THEN '   '                            
                  ELSE 'BILLION ,   '                              
                END                                                
             WHEN  4  THEN                                         
                CASE STR                                           
                  WHEN '000' THEN '   '                            
                  ELSE 'MILLION ,   '                              
                END                                                
             WHEN  5  THEN                                         
                CASE STR                                           
                  WHEN '000' THEN '   '                            
                  ELSE 'THOUSAND ,   '                             
                END                                                
             ELSE                                                  
                '            '                                     
           END                                                     
     FROM CTETAB WHERE COUNT > 0;    

Table Partitioning in DB2 Express

Hi,

It's been a while since my last post and I want to apologize, this one is about DB2 LUW ... (although everything here behaves the same on zOS if I'm not mistaken).

A customer I was helping today had a problem with one of his tables. He has a really big table that keeps getting records inserted into, and he needs to periodically delete old records (just a plain log table), and the delete operation takes forever.

Another problem is that client is using DB2 Express, so he is not able to use table partitioning to solve this problem (by deleting partitions instead of rows).
As a solution I recommended him to use a mechanism I remembered from zOS of partitioned views. A partitioned view is a view that performs a UNION ALL operation against some tables.
The special feature of a partitioned view is that the DB2 compiler and optimizer knows how to deal with it very efficiently, which means, when you insert a row to the view, DB2 will know into which table to insert the row and when you select rows from the view, DB2 will access only the relevant base tables, and not all of them (just like partition pruning).

How does this magic work? when you create the partitioned view, you base it on tables with check constraint. The check constraints of the underlying tables must not overlap because these check constraints tells DB2 to which table every row belongs.

Let's look at an example:

create table t1 (id int, rtype int check(rtype=1));
create table t2 (id int, rtype int check(rtype=2));

create view v as
select * from t1 where rtype=1
union all
select * from t2 where rtype=2;

-- basically you don't have to code the where clauses in the view definition itself, but it is possible that it provides the optimizer with more information for run time optimizations so I would leave it there

insert into v values (1, 1);
insert into v values (1, 2);
insert into v values (2, 1);
insert into v values (2, 2);

-- at this point, the rows were inserted into the appropriate tables

select * from v;
select * from t1;
select * from t2;

-- now lets make sure that DB2 access only the relevant tables for select processing:
create index idx1_t1 on t1 (rtype);
create index idx2_t2 on t2 (rtype);

-- the following query will show us that the indexes were not used yet
select INDEX_SCANS from table (mon_get_index('','T1',-2));
select INDEX_SCANS from table (mon_get_index('','T2',-2));

-- now we issue the select
select rtype from v where rtype=1;

-- running the above queries against mon_get_index will show that the index of T1 was scanned once

select rtype from v;

-- running the above queries against mon_get_index will show that the index of T1 was accessed again and that the index of T2 was accessed for the first time


There are many advantages to this design, for example:
1) You can change underlying table design without changing the application as long as you can make sure the view reflects the same results
2) Each underlying table can belong to a different tablespace with different characteristics on different storage
3) Each table can have different indexes
4) Removing / Adding bulks of data is very easy by simply changing the view to include / exclude new / old tables

etc...

I think this feature is amazing and it can be used as a replacement for table partitioning, especially in the express edition which does not support table partitioning.

I have also found a very interesting paper on this feature that was written way back at 2002 but I still recommend reading it, if you intend to use this feature, or want to learn more about it.
http://www.ibm.com/developerworks/data/library/techarticle/0202zuzarte/0202zuzarte.pdf

At first I though to title this post as - A poor man's partitioning, but now I think this feature is too cool to be named like that

I talked to much already,
Have fun with this feature and take care

Momi