How to convert 21 digits to 13 digits uniquely

[lynnliu]

Hi,
I have a key filed storing 21 digital number in source table, the target table key field only can store 13 digits in length. How can i convert the string length from 21 to 13 or less ,keep uniquely? it's out of range when converting the decimal number to hexdecimal.

can i use CRC32 function? Is it possible the same output from CRC32?

Best Regards!
Thanks!

[ogmios]

How can I cram 1 liter of water into a 0.5 liter bucket and still keep the 1 liter... nope. It doesn't work that way.

You could try something as CRC32 or hashing but you will never be able to guarantee uniqueness.

Either change the target or the source.

Ogmios

P.S. Unless of course not all of the 21 digits of the source are actually used.

[ray.wurlod]

Let me rephrase your question.

There is a field that can contain potentially 10^21 different values; you want to store these in a field that can contain a maximum of 10^13 distinct values.

Unless there is other information about the original values that you have not revealed, then the answer is no.

[lynnliu]

Thanks Ogmios and Ray for helping

Unfortunately the customer insists that the source and target table can not change. The further information is that the 13digitals field is only for a primary key to distinct records(to distinct these 21digits strings more exact, cause they have some other tables contain the 21digits key also need to do this convertion).

Do i make that clear? So is there a way to create unique 13digitals string mapping 21digits string? Or is there another way do that?

The job extracts data from source table and loads to target table runs everyday.


Thanks a million!

[lynnliu]

a little change! the 21digits can cut down to 19digits.

Can datastage do thirty decimal? If ok , i will divided the 21digis string to two parts ---- one 10digits, another 9digits , thirty decimal them separately, then compile the two results. It would be bad performance i think

[ray.wurlod]

Generate a value into the 13 digit field.

Ignore the incoming 21-digit field, or store it in a non-key column in the target table if that's allowed for.

Require your customer to justify this illogical decision.

[lynnliu]

thanks!

I've another question. What the range of decimal number DataStage can handle? i find the we get the wrong result when the decimal number bigger then 10digits.

[ray.wurlod]

The maximum precision that DataStage can handle is configured using the EXACTNUMERIC tuneable. Its default value is 15; you can take it as high as 57.

The largest four-byte signed integer is 2,147,483,647 (ten digits); this has nothing to do with DataStage and everything to do with the largest integer number that can be represented using 31 bits. (One bit is used for the sign.)

Non-integer numbers within DataStage are stored using an eight-byte IEEE standard for floating point numbers. The largest such is approximately 10 to the power 308, though the number of accurate significant digits remains constrained by EXACTNUMERIC (and, possibly, by a PRECISION setting within a DataStage BASIC routine).

If that's not enough, you can do "string math" (the way we used to do it at school) on numeric character strings of effectively unlimited length, using functions SADD, SSUB, SMUL and SDIV.

[roy]

Hi,
Well I may have a solution for you if your trget column is of a char type.
since you said 13 digit number I assume your target column is character and not numeric.
This was actually a question in one of my first job inteview questions and I hope it will benefit you since few people I asked got the answer as did I.
I'll simplify the question:
the basic problem is having a large number to be reperesented in a small space or should I say small number of bytes.
the eventual solution is to convert the decimal number to a larger exponent representation such as hexadecimal.
if you have a character target column use 0-F convention.
since 13 times F in hexa is still not enough who says you can't design a 32 base conversion (with capital/lower case letters).
0,1,... ,9, A , B ,... ,Z
0,1,... ,9, 10,11,..., 35
the sky is the limit

IHTH and good luck ,

[ray.wurlod]

The original post did specify 13 digits - I also thought of this kind of encoding, but felt constrained by that original requirement. Experience teaches that you should avoid the letters I and O if you do go for this solution.

[lynnliu]

Thanks Ray and Roy.
Ray, I am sorry for my bad expression. The 13 length target column is character.

I've written a routine before ,but i don't know how to large the precision of the number, so it can not handle the larger number bigger than 10digits. How to make it do the 19digits number?

Code: Select all

FUNCTION FreeDecimal(SourceNumber,BaseNumber)
Ans = "" 
x = SourceNumber
loop
    r = Mod(x,BaseNumber)
    x = Div(x,BaseNumber)
    if r < 10 then 
       Ans = r : Ans
    end
    else
       Ans = Char(r+55) : Ans
    end
while  x > 0 
repeat
RETURN(Ans)

[ray.wurlod]

Why not just convert to hexadecimal?
A 21-digit decimal number made up entirely of "9" reduces to DE9FFFFF (that is, eight characters) in hexadecimal.

The conversion is easy. Use

Code: Select all

Oconv(TheNumber, "MX") 

to convert to hexadecimal.

[lynnliu]

Ray,
i have tried this way and Dtx(), but DataStage output is Dword(32bit) , so the results are not unique, for example Oconv(2313682943,"MX") the same reult "89E7FFFF", the output loops at interval of 2,147,483,647 * 2. how to generate a Qword(64bit) result.
Do i explain clear?

Thanks

[ray.wurlod]

Probably a bit more math needed; break the decimal number using integer division and remainder, and convert the pieces.

[mhester]

There are many math websites that address your very concern. If you google for this you will return a dozen sites. There is one that even discusses your very requirement. You may wonder why Ray said to avoid I and O and it's because these can be confused with 1 and 0 - other than this confusion they are perfectly fine to use.

Regards,