embed mainframe sign to numeric fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

embed mainframe sign to numeric fields

Author

Topic

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2001-10-08 : 16:12:42

Some of you may have a need to embed mainframe signed numerics into fields prior to uploading data to a mainframe. After perusing this site and getting help from people like NR today and graz and some others a while back I have coded this little SP to add to my toolkit. This was a lot of fun and I hope it helps someone out.

First you must create a translation table. Signed numeric field on the mainframe use a special code in the last digit to denote not only the last digit but the sign of the number as well.


CREATE TABLE [SIGN_CONVERSION] (
	[CENT_VALUE] [char] (1) NOT NULL ,
	[POS_SYMBOL] [char] (1) NOT NULL ,
	[NEG_SYMBOL] [char] (1) NOT NULL 
)
GO

CENT_VALUE POS_SYMBOL NEG_SYMBOL 
---------- ---------- ---------- 
0          {          }
1          A          J
2          B          K
3          C          L
4          D          M
5          E          N
6          F          O
7          G          P
8          H          Q
9          I          R

Insert the above values into the table.

Now for the code. Since I need to do this quite a bit I decide to settle for dynamic SQL. The values you need to pass are tablename, column name, and the number of zero filled digits you want in return.



DECLARE @DEC	as	varchar(2),
@field1         as      varchar(25),
@tablename      as      varchar(25),
@execstring     as      varchar(2048)

select @field1 = 'amount'
select @tablename = 'user_test'
select @DEC = '10'

select @execstring = "update " + @tablename + " 
set " + @field1 + "= case
when (left(rtrim(" + @field1 + "),1) <> '-') then
   (select RIGHT(replicate('0'," + @DEC + " + replace((left(" + 
@field1 + ",len(" + @field1 + ")-1) + POS_SYMBOL),'.','')," + @DEC + ")
   FROM INTF_SIGN_CONVERSION 
   WHERE CENT_VALUE = right(rtrim(" + @field1 + "),1))
else
  (select RIGHT(replicate('0',"+ @DEC + " + replace(replace((left(" 
+ @field1 + ",len(" + @field1 + ")-1) + NEG_SYMBOL),'.',''),'-','')," + @DEC+ ")
   FROM SIGN_CONVERSION 
   WHERE CENT_VALUE = right(rtrim(" + @field1 + "),1))
end
"
exec (@execstring)

Now for a look at the query without all of the variable substitution and string manipulation


update user_test 
set amount = case
when (left(rtrim(amount),1) <> '-') then
   (select RIGHT('0000000000' + 
replace((left(amount,len(amount)-1) + POS_SYMBOL),'.',''),10)
   FROM INTF_SIGN_CONVERSION 
   WHERE CENT_VALUE = right(rtrim(amount),1))
else
  (select RIGHT('0000000000' + 
replace(replace((left(amount,len(amount)-1) + NEG_SYMBOL),'.',''),'-',''),10)
   FROM SIGN_CONVERSION 
   WHERE CENT_VALUE = right(rtrim(amount),1))
end

Thanks again to all who have helped me in the past, I hope this helps someone else.

Oh, and BTW, you can use this same technique to convert from mainframe signed data to natural numbers. I'll be writing that technique for myself later today. I'll leave that code out as an exercise for the reader.

Cat

Edited by - cat_jesus on 10/08/2001 16:16:19

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources