How to convert 21 digits to 13 digits uniquely
Moderators: chulett, rschirm, roy
How to convert 21 digits to 13 digits uniquely
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!
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!
Re: How to convert 21 digits to 13 digits uniquely
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.
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.
In theory there's no difference between theory and practice. In practice there is.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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!
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!
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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 ,
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 ,
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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?
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)
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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. Useto 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")
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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,
Regards,
Mike Hester
mhester@petra-ps.com
mhester@petra-ps.com