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.
| Author |
Topic |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
 Posted - 2007-08-23 : 19:51:33
|
This nifty function convert COBOL value to equivalent integer.If last characer is one of these alphanumeric charactersJ -1K -2L -3M -4N -5O -6P -7Q -8R -9} -0 it means that complete value is negative and last character is the equivalent numeric in table above.CREATE FUNCTION [dbo].[fnCobol2Int]( @Item VARCHAR(18))RETURNS BIGINTASBEGIN RETURN CAST( CASE WHEN @Item LIKE '%[^}jklmnopqr0123456789JKLMNOPQR]%' THEN NULL WHEN LEFT(@Item, LEN(@Item) - 1) LIKE '%[}jklmnopqrJKLMNOPQR]%' THEN NULL WHEN RIGHT(@Item, 1) LIKE '[}jklmnopqrJKLMNOPQR]' THEN '-' ELSE '' END + LEFT(@Item, LEN(@Item) - 1) + CHAR(47 + CHARINDEX(RIGHT(@Item, 1), '}JKLMNOPQR0123456789}jklmnopqr') % 10) AS BIGINT)END
E 12°55'05.25"N 56°04'39.16" |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-08-23 : 22:12:08
|
In COBOL speak:PROGRAM-ID. PROG-1.....WORKING-STORAGE SECTION.01 MY-DATA PIC S9(9)V99 DISPLAY. There are actually many ways of representing numbers in COBOL, so if you have a complex file to load into SQL, you may need a product like Data Junction, or may need to do a custom extract program.A few examples:PROGRAM-ID. PROG-1.....WORKING-STORAGE SECTION.01 MY-DATA1 COMP-1.01 MY-DATA2 COMP-2.01 MY-DATA3 PIC S9(5) COMP-3.01 MY-DATA8 PIC S9(9)V99 DISPLAY SIGN IS LEADING SEPARATE CHARACTER.01 MY-DATA9 PIC S9(9)V99 DISPLAY SIGN IS TRAILING SEPARATE CHARACTER. COMP-3 (Packed Decimal) is very common:http://www.discinterchange.com/TechTalk_Packed_fields_.html"The Binary Coded Decimal (BCD) data type is just as its name suggests -- it is a value stored in decimal (base ten) notation, and each digit is binary coded. Since a digit only has ten possible values (0-9), it can be represented in binary form with only 4 bits. Four bits is called a "nybble", and each nybble contains one digit of the value. Therefore, you can get two digits in each 8 bit byte. (There's an example below). Normal character representation only stores one character (digit) per byte, so packed data only requires half the storage of unpacked (character) data. (See Character, BCD, and Binary Fields if this description is not clear.) The value in a comp-3 field is stored high-order to low-order. That is, the upper nybble of the first byte encountered in the file is the most significant digit of the value, the lower nybble of that byte is the next digit, etc., etc. The last nybble -- the low nybble of the least significant byte -- is used to store the sign for the number. Unlike IBM Signed fields (see EBCDIC to ASCII Conversion of Signed Fields), this nybble stores only the sign, not a digit. "C" hex is positive, "D" hex is negative, and "F" hex is unsigned. In COBOL comp-3 fields (and in most other languages) this nybble is reserved for the sign whether or not the field is denoted as a signed field in the COBOL PIC. "CODO ERGO SUM |
 |
|
|
|
|
|
|
|
Cobol conversion