DB2 Compare numeric values in a char column

Consider the following scenario. You have a column which is defined as CHAR but contains numeric values. Your task is to select the rows which has values greater than an integer constant.
Here is how you can compare text values to integer in DB2

T1 is the table with a CHAR(2) column C1

SELECT * FROM T1;
---------+---------+-
C1                   
---------+---------+-
6
--
                    
 6                   
30                   
1                   
 1                

Do note that the column has null (2nd row) and spaces(3rd row). So we need to accommodate for that. The CAST function we use would work with NULL values, but if there are SPACES, it will throw SQL error -420.
You can achieve the objective with below query

SELECT * FROM T1 WHERE CAST(REPLACE(C1,'  ','00') AS INT) > 5;
---------+---------+---------+---------+---------+---------+--
C1                                                            
---------+---------+---------+---------+---------+---------+--
6                                                             
 6                                                        
30                                                

Note : When using replace function make sure you replace for the whole length of the Char field else you would end up with unexpected results. 

DB2 Aggregate function behavior with Null Values in Table

Aggregate functions are very useful in getting meaningful information from a group. 
Here we are looking at what will happen if the table has NULL values. Aggregate functions will ignore the Null values in the column. 

Consider a Table TEMP1 with two columns
C1 - CHAR(1)
C2 - Integer

The Values in the table are 

C1	C2
A	100
B	NULL
A	200
B	100
B	400
C	NULL
C	NULL

SELECT C1, SUM(C2) SUM_C2,     

           MIN(C2) MIN_C2,     

           MAX(C2) MAX_C2,     

           AVG(C2) AVG_C2,     

           COUNT(*) COUNT_STAR,

           COUNT(C2) COUNT_C2  

  FROM SESSION.TEMP1           

  GROUP BY C1;   

Result will be 

C1       SUM_C2       MIN_C2       MAX_C2       AVG_C2   COUNT_STAR     COUNT_C2

---------+---------+---------+---------+---------+---------+---------+---------+

A           300          100          200          150            2            2

B           500          100          400          250            3            2

C   -----------  -----------  -----------  -----------            2            0

As you can see only Count(*) considers the rows with the Null values. Rest all functions ignore the Null Values.

Hope this helps you guys out there.        

     

Removing spaces from delimited record

When you create a delimited file in COBOL, you are stuck with the fact that the extra spaces in a field is there to stay. Removing those unwanted spaces and squeezing the data using COBOL is a tricky task, which am not sure if it will even be possible. 

This brings me to achieving this result using SORT. 

Below example shows a semicolon delimited file created by a COBOL reporting program. 

We have the first step adding Quotes to fields which is supposed to have spaces in them like name, address etc. Do make sure that when you create your COBOL layout, have an additional leading and trailing space for the quotes. Else the field might get truncated on addition of quotes.

The second step uses the quotes to not squeeze the valid spaces in a field.

//*****************************************************************

//STEP0001 EXEC PGM=SORT

//*****************************************************************

//*  

//SORTIN   DD *   

123   ;SAMUEL JACKSON     ; ALABAMA ST  ;23456 

1456  ;PETER SAMUEL       ; DO NOT MAIL ;

2343  ;HARRY POTTER       ; OHIO ST     ;23356 

/*           

//SORTOUT  DD DSN=&&TEMP,            

//            DISP=(,PASS,DELETE),           

//            UNIT=SYSDA,                    

//            SPACE=(CYL,(1,1),RLSE),         

//            DCB=(RECFM=FB,LRECL=50,BLKSIZE=0)       

//SYSIN    DD *       

  OPTION COPY             

  OUTREC FIELDS=(1,7,

                 8,19,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"'),27,1,

                 28,13,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"'),41,6)
//SYSOUT   DD SYSOUT=* 

//SYSPRINT DD SYSOUT=*

//*         

//******************************************************************

//STEP0002 EXEC PGM=SORT 

//******************************************************************

//*      

//SORTIN   DD DISP=SHR,DSN=&&TEMP       

//SORTOUT  DD SYSOUT=*           

//SYSIN    DD *                    

  OPTION COPY         

  INREC FIELDS=(1,50,SQZ=(SHIFT=LEFT,PAIR=QUOTE))

  OUTREC FINDREP=(IN=C'"',OUT=C'')            

//SYSOUT   DD SYSOUT=*                     

//SYSPRINT DD SYSOUT=*                           

//*          

Output after Step 1

123   ;"SAMUEL JACKSON"   ;"ALABAMA ST" ;23456 

1456  ;"PETER SAMUEL"     ;"DO NOT MAIL";      

2343  ;"HARRY POTTER"     ;"OHIO ST"    ;23356 

Output after Step 2

123;SAMUEL JACKSON;ALABAMA ST;23456   

1456;PETER SAMUEL;DO NOT MAIL;        

2343;HARRY POTTER;OHIO ST;23356       

Note: All this effort to save space will help only if you convert this to a VB file
Use the below Sort step to do that

  OPTION COPY   

  OUTFIL FTOV,VLTRIM=C' ' 

Get Running total in Db2

Using an aggregate function in SQL is pretty straightforward. However we sometimes need to have other columns in a table along with the corresponding aggregate we are trying to calculate. This is not possible if we use the functions in the traditional way using group by clause. 

Lets take an example. Below is the table BookSales. 

BookID	DateOfSale	SaleCnt
1001	2016-01-01	33
1001	2016-01-02	10
1001	2016-01-03	16
1002	2016-01-04	22
1001	2016-01-05	12
1001	2016-01-06	20
1002	2016-01-07	10
1001	2016-01-08	23
1002	2016-01-09	12
1002	2016-01-10	50
1001	2016-01-12	14
1001	2016-01-13	24

What I want to find is the running total for each book along with the dates. The result I am expecting is something like this

BookID	DateOfSale	SaleCnt	RunningTot
1001	2016-01-01	33	33
1001	2016-01-02	10	43
1001	2016-01-03	16	59
1001	2016-01-05	12	71
1001	2016-01-06	20	91
1001	2016-01-08	23	114
1001	2016-01-12	14	128
1001	2016-01-13	24	152
1002	2016-01-04	22	22
1002	2016-01-07	10	32
1002	2016-01-09	12	44
1002	2016-01-10	50	94

Query

SELECT A*,

       SUM(SALECNT) OVER(PARTITION BY BOOKID

                         ORDER BY DATEOFSALE) AS RUNNINGTOT

FROM BOOKSALES A;

You could have the same result even if we change the display order using OrderBy clause in the query.

For Example

SELECT A*,

       SUM(SALECNT) OVER(PARTITION BY BOOKID

                         ORDER BY DATEOFSALE) AS RUNNINGTOT

FROM BOOKSALES A

ORDER BY BOOKID, DATEOFSALE DESC;

---------------------------- RESULTS IN ------------------------------

BookID	DateOfSale	SaleCnt	RunningTot
1001	2016-01-13	24	152
1001	2016-01-12	14	128
1001	2016-01-08	23	114
1001	2016-01-06	20	91
1001	2016-01-05	12	71
1001	2016-01-03	16	59
1001	2016-01-02	10	43
1001	2016-01-01	33	33
1002	2016-01-10	50	94
1002	2016-01-09	12	44
1002	2016-01-07	10	32
1002	2016-01-04	22	22