Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Hexadecimal Data Conversion to Integer

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
smdayaz.tpt

New User


Joined: 04 Oct 2007
Posts: 5
Location: Bangalore

PostPosted: Mon Jul 28, 2008 3:28 pm    Post subject: Hexadecimal Data Conversion to Integer
Reply with quote

Hi All,

I have a Hexadecimal value in VARCHAR(2) FOR BIT DATA, I need the Hexadecimal Value to be converted to Integer.

For Eg:

Code:
DECLARE tempvar VARCHAR(4) FOR BIT DATA.  This contains value x'00005721', This needs to be converted as
           (5*16^3)+(7*16^2)+(2* 16^1)+(1* 16^0) = 22305 (Integer)

Please help me in finding the solution.

Thanks In Advance,
Ayaz
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Jul 28, 2008 8:42 pm    Post subject:
Reply with quote

Hello,

Quote:
Please help me in finding the solution.

Once you have read the varchar data into a variable, you could redefine the variable as a cobol binary number (COMP) and you would not need to do any calculation.

I'm not sure how this is a DB2 question. Possibly there is something i misunderstand.

If you clarify your requirement someone here may have a suggestion.
Back to top
View user's profile Send private message
smdayaz.tpt

New User


Joined: 04 Oct 2007
Posts: 5
Location: Bangalore

PostPosted: Tue Jul 29, 2008 10:43 am    Post subject: Reply to: Hexadecimal Data Conversion to Integer
Reply with quote

Hi Dick,

I think i have not clearly raised my query...Actually we are dealing in the stored Proc conversion which is coded in the SQL SERVER those PROC's needs to be converted to the DB2 and we are using the IBM DB2 Migratoin Tool kit(MKT) to do so.

There we found an issue where a variable declared in SQL server PROC has the data type of VARBINARY which the MKT converted it to the VARCHAR(4) BIT DATA which is a Equivalent data type in DB2.

The actual Problem here is, in SQLSERVER Stored PROC they CAST the VARBINARY to the INTEGER data type for the equivalent the MKT tool has to convert from the VARCHAR(4) for bit data to the Integer data type.The MKT thows an error that "The translation not succeeded" Can you give me a suggestion how we able to do this in DB2.

Here comes the CODE:

"We are facing the PROBLEM in the CASTING" I'm not sure casting is supported in DB2...


Code:
 SQL SERVER STORED PROC
--| CREATE PROCEDURE GetUniqueNumFromBinary
--|    -- Add the parameters for the stored procedure here
--|    @bindata varbinary(8) ,
--|    @uniquenum int output,
--|    @userid tinyint output,
--|    @databaseid tinyint output
--| AS
--| BEGIN
--|    -- SET NOCOUNT ON added to prevent extra result sets from
--|    -- interfering with SELECT statements.
--|    SET NOCOUNT ON;
--|    declare @tempunqbin varbinary(4)
--|    declare @tempuserbin varbinary(2)
--|    declare @tempdbbin varbinary(2)
--|     -- Insert statements for procedure here
--|    
--|    set @tempunqbin= substring(@bindata,8,1)+substring(@bindata,7,1)+substring(@bindata,6,1)+substring(@bindata,5,1)
--|    set @uniquenum = convert(int,@tempunqbin)
--|    
--|    set @tempuserbin = substring(@bindata,4,1)+substring(@bindata,3,1)
--|    set @userid  = convert(int,@tempuserbin)
--|    
--|    set @tempdbbin  = substring(@bindata,2,1)+substring(@bindata,1,1)
--|    set @databaseid  = convert(int,@tempdbbin)
--| END


DB2 Stored Proc:


DROP PROCEDURE GetUniqueNumFromBinary (VARCHAR(8) FOR BIT DATA,
                                       INTEGER,
                                       SMALLINT,
                                       SMALLINT )!

CREATE PROCEDURE GetUniqueNumFromBinary (v_bindata VARCHAR(8) FOR BIT DATA,
                                          OUT v_uniquenum INTEGER,
                                          OUT v_userid SMALLINT,
                                          OUT v_databaseid SMALLINT )
LANGUAGE SQL

BEGIN
   
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
   
    DECLARE l_error CHAR(5) DEFAULT '00000';
   
    DECLARE v_tempunqbin VARCHAR(4) FOR BIT DATA;
   
    DECLARE v_tempuserbin VARCHAR(2) FOR BIT DATA;
   
    DECLARE v_tempdbbin VARCHAR(2) FOR BIT DATA;
   
    DECLARE CONTINUE HANDLER FOR NOT FOUND
       
        SET l_error = '00000';
   
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING
       
        BEGIN
           
            SET l_error = SQLSTATE;
           
            IF SUBSTR(l_error, 1, 1) >= '5'
               AND SUBSTR(l_error, 1, 1) <= '9' THEN
               
                RESIGNAL;
           
            END IF;
       
        END;
   
    SET v_tempunqbin = CAST (MSSQL.SUBSTRINGBIN(v_bindata, 8, 1) AS VARCHAR(1) FOR BIT DATA) || CAST (MSSQL.SUBSTRINGBIN(v_bindata, 7, 1) AS VARCHAR(1) FOR BIT DATA) || CAST (MSSQL.SUBSTRINGBIN(v_bindata, 6, 1) AS VARCHAR(1) FOR BIT DATA) || CAST (MSSQL.SUBSTRINGBIN(v_bindata, 5, 1) AS VARCHAR(1) FOR BIT DATA);
   
    SET v_uniquenum = CAST (v_tempunqbin AS INTEGER);
   
    SET v_tempuserbin = CAST (MSSQL.SUBSTRINGBIN(v_bindata, 4, 1) AS VARCHAR(1) FOR BIT DATA) || CAST (MSSQL.SUBSTRINGBIN(v_bindata, 3, 1) AS VARCHAR(1) FOR BIT DATA);
   
    SET v_userid = CAST (v_tempuserbin AS INTEGER);
   
    SET v_tempdbbin = CAST (MSSQL.SUBSTRINGBIN(v_bindata, 2, 1) AS VARCHAR(1) FOR BIT DATA) || CAST (MSSQL.SUBSTRINGBIN(v_bindata, 1, 1) AS VARCHAR(1) FOR BIT DATA);
   
    SET v_databaseid = CAST (v_tempdbbin AS INTEGER);

END!


Please help me in finding the Solution.

Thanks In advance,
Ayaz
Back to top
View user's profile Send private message
NidhiSuyog

New User


Joined: 25 Apr 2008
Posts: 1
Location: bangalore

PostPosted: Tue Jul 29, 2008 11:49 am    Post subject: Reply to: Hexadecimal Data Conversion to Integer
Reply with quote

CREATE FUNCTION HexStringToINT (
@Hex varchar(255))
RETURNS int
AS
BEGIN
declare @Char char(1), @Multiply int, @Value int, @Ans int
set @Hex = reverse(@Hex)
select @Ans = 0
, @Multiply = 0
while @Multiply + 1 <= datalength(@Hex) begin
set @Char = substring(@Hex,@Multiply + 1,1)
if @Char between '0' and '9'
set @Value = cast(@Char as int)
else
select @Value = case @Char when 'A' then 10
when 'B' then 11
when 'C' then 12
when 'D' then 13
when 'E' then 14
when 'F' then 15
else 0
END
set @Ans = @Ans + (@Value * power(16,@Multiply))

set @Multiply = @Multiply + 1
end
return @Ans
END
GO
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Export flat file data into excel sheet murali.andaluri DFSORT/ICETOOL 2 Mon Mar 20, 2017 5:39 pm
No new posts Append data from two files into a sin... Praveen04 DFSORT/ICETOOL 5 Thu Mar 16, 2017 7:29 pm
No new posts SPUFI -- Joining 3 tables – data in... Sysaron DB2 2 Wed Mar 08, 2017 4:18 am
No new posts Overriding PS data in rexx Shaheen Shaik CLIST & REXX 8 Fri Mar 03, 2017 5:08 pm
No new posts JCL to get submitted once dataset has... vinu78 JCL & VSAM 17 Thu Feb 16, 2017 7:32 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us