Re: Converting Character Zoned Data to Numeric in SQL

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

I did try one of my other suggestions - here is the SELECT statement - 
the file named there has a single 5-character alpha field with 2 
records, one with "0001L" and the other with "00014". The first one is -13.

This statement gets the values right - it first gets the sign ( 1 or -1) 
and multiplies that by the quantity (all but the last character 
converted to zoned, multiplied by 10, and the last character from a case 
added to that result). I know - brute force method!!

SELECT                                                     
case                                                       
 when substr(chr1,length(chr1),1) between '0' and '9'then 1
 else -1                                                  
end *                                                      
(                                                          
 zoned(substr(chr1,1,length(chr1)-1)) * 10 +              
 case substr(chr1,length(chr1),1)                         
   when '}' then 0                                        
   when 'J' then 1                                        
   when 'K' then 2                                        
   when 'L' then 3                                        
   when 'L' then 4                                        
   when 'L' then 5                                        
   when 'L' then 6                                        
   when 'L' then 7                                        
   when 'L' then 8                                        
   when 'L' then 9
   else 0        
 end             
)                 
FROM vern/chrtozon

rob@xxxxxxxxx wrote:
> There is no BITAND yet available in SQL for DB2 for i.  Submit a DCR and 
> see what happens.
> http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzscale.htm
>
> https://www-912.ibm.com/r_dir/ReqDesChange.nsf/Request_for_Design_Change?OpenForm
>
>
> Rob Berendt
>