I have made a stored procedure, containing this part for generating a unique
reference number.
SET i = 0;
REPEAT
SET i = i + 1;
SELECT RAND()
INTO reference
FROM SYSIBM.SYSDUMMY 1;
SET p_reference = ref_prefix || SUBSTR(CAST(ref erence AS CHAR(12)),3);
SELECT cardno
INTO cardno
FROM HTTP.TB00008 WHERE reference = p_reference;
UNTIL (cardno IS NULL OR i = 1000)
END REPEAT;
IF (i = 1000) THEN
SIGNAL c_failed_refere nce
SET MESSAGE_TEXT = 'Unique reference could not be generated in a 1000
attempts';
END IF;
Unfortunately it happens from time to time, that it fails generating a
unique reference in a 1000 attempts.
Is the RAND() function not unique enough, and what can I do else ?
Thanks in advance
Poul 6 5544
You try a 1000 times to find a gap in your card no? Are the rows missing
or is cardno truly NULL in teh row? If the later, what about this:
How about this:
SELECT reference INTO p_reference
FROM
(SELECT reference,
rownumber() over () as rn,
count(*) over cnt
FROM HTTP.TB00008
WHERE cardno IS NULL) AS X
WHERE rn = integer(rand() * cnt) + 1
This will find you a random gap.
Cheers
Serge
Small correction: SELECT reference INTO p_reference FROM (SELECT reference, rownumber() over () as rn, count(*) over cnt FROM HTTP.TB00008 WHERE cardno IS NULL) AS X,
TABLE(VALUES(ra nd())) AS R(rand) WHERE rn = integer(rand * cnt) + 1
Cheres
Serge
> You try a 1000 times to find a gap in your card no? Are the rows missing or is cardno truly NULL in teh row? If the later, what about this: How about this: SELECT reference INTO p_reference FROM (SELECT reference, rownumber() over () as rn, count(*) over cnt FROM HTTP.TB00008 WHERE cardno IS NULL) AS X WHERE rn = integer(rand() * cnt) + 1
No, I'm making a unique reference that hasn't already been used, at the time
of inserting.
cardno is a not null field generated in a triggered sequence.
So if the select cardno from http.tb0008 where reference = "generated value"
gives no result,
I have found one.
The escaping after a 1000 attempts is a temporary one to prevent looping
forever, when it fails finding a unique one.
I'm just surprised that the select rand() is giving the same result a 1000
times in a row.
Cheers
Poul
Poul Møller Hansen wrote: I have made a stored procedure, containing this part for generating a unique reference number.
Does it need to be a random number? Why not use a sequence? If you
have a big 'stride' which is mutually prime with the number of entries
in the sequence (eg - if the sequence ranges over 1,000,000 to
1,000,000,000, then 31,572,493 is prime relative to 999,000,000 and
will generate numbers such as 32,572,493 followed by 64,144,986 and
95,717,479 and 127,289,972 and so on. The 'mutual prime' property
ensures that every value in the range will be used eventually.
--
Jonathan Leffler #include <disclaimer.h >
Email: jl******@earthl ink.net, jl******@us.ibm .com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
Poul,
the easiest way to get unique numbers is to use a sequence, which
gives you consecutive numbers, i.e. 1,2,3,...
if you need unique numbers to be not consecutive, you might want to
play with swapping bytes and / or bits.
For instance, get numbers 0x00000001, 0x00000002, 0x00000003,...
and swap bytes to 0x01000000, 0x02000000, 0x03000000,...
You might also read Donald Knuth's book, where he describes basics of
random numbers generating
To measure the number of repeats in a small rand() sample of
size 10,000, one can do something like this on UDB:
create table samp ( i integer ) ;
insert into samp
with temp(n) as ( values( 1 )
union all
select n + 1 from temp
where n < 10000 )
select n from temp ;
commit ;
select count( * ) from samp ;
select count( distinct rand(1) ) from samp ;
select count( distinct rand(5) ) from samp ;
select count( distinct rand(7) ) from samp ;
The desired result is 10,000 from each select.
-Gordon
"Poul Møller Hansen" <ph@pbnet.dk> wrote in message
news:HQ******** ******@news.get 2net.dk... I have made a stored procedure, containing this part for generating a unique reference number.
SET i = 0; REPEAT SET i = i + 1; SELECT RAND() INTO reference FROM SYSIBM.SYSDUMMY 1; SET p_reference = ref_prefix || SUBSTR(CAST(ref erence AS CHAR(12)),3); SELECT cardno INTO cardno FROM HTTP.TB00008 WHERE reference = p_reference; UNTIL (cardno IS NULL OR i = 1000) END REPEAT; IF (i = 1000) THEN SIGNAL c_failed_refere nce SET MESSAGE_TEXT = 'Unique reference could not be generated in a 1000 attempts'; END IF;
Unfortunately it happens from time to time, that it fails generating a unique reference in a 1000 attempts. Is the RAND() function not unique enough, and what can I do else ?
Thanks in advance
Poul
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: lallous |
last post by:
Hello,
This code works fine when 'size' is less than 32768 however when size is
bigger this function never returns.
Can't find out why?!
If I break into the code I can see that 'i' is 32768....
void MakeRandomArray(unsigned long **a, unsigned long size)
{
unsigned long *data = new unsigned long ;
double sizef = (double)(size - 1);
|
by: Justin Lebar |
last post by:
Sorry about the huge post, but I think this is the amount of
information necessary for someone to help me with a good answer.
I'm writing a statistical analysis program in ASP.net and MSSQL7 that
analyzes data that I've collected from my business's webpage and the
hits it's collecting from the various pay-per-click (PPC) engines.
I've arrived at problems writing a SQL call to generate certain
statistics.
Whenever someone enters our...
|
by: Stan |
last post by:
Is it possible to hash a 100 bytes string to a integer? I found a few .NET
classes for that
such as Sha1Managed.ComputeHash but they return bytes. I am just not sure
about the idea of converting 100 bytes to four or eight without loosing
uniqueness.
The issue has come up because I am storing bills with customers in database
and I would like to reuse customers, so that not every bill has its own
customer. In order to do that I need to...
|
by: eric.gagnon |
last post by:
In a program randomly generating 10 000 000 alphanumeric codes of 16
characters in length (Ex.: "ZAZAZAZAZAZAZ156"), what would be an
efficient way to ensure that I do not generate duplicates?
STL set, map?
Could you give me a little code example?
Thank you.
|
by: Mark |
last post by:
BEGINNER QUESTION
I have a table which has a compound primary key consisting of two columns.
One of these columns is a foreign key which is generated in another table by
an identity.
I want to be able to generate the other primary key column value
automatically when an insert occurs but assume that I cannot use an identity
because it would have to be unique for this table.
| |
by: Lukas Meusburger |
last post by:
i have a problem auto-assigning mnemonics when dynamicly generating menues.
in some cases, two memorie entries get the same mnemonic key assigned
because the words begin with the same letter. this means, that they are more
or less useless
does anyone have an algorithm or some kind of code i can use to make sure
the mnemonic keys are unique when auto-assigning them
thank's!
|
by: Velhari |
last post by:
Hi,
I am a beginner. Please tell me, For generating Random Numbers, Initially why we are going for seed method.
And another question is that, I want to print unique random number how to print by using rand() method to print unique random number.
And finally i want to know, If a program generate random numbers and the same program will execute after 100 or some days, it will not to generate the old random number (i.e the random number...
|
by: Steven D'Aprano |
last post by:
I have an application that will be producing many instances, using them
for a while, then tossing them away, and I want each one to have a unique
identifier that won't be re-used for the lifetime of the Python session.
I can't use the id() of the object, because that is only guaranteed to be
unique during the lifetime of the object.
For my application, it doesn't matter if the ids are predictable, so I
could do something as simple as...
|
by: Gary Hasler |
last post by:
Does anyone have any suggestions on generating GUID (Globally Unique
Identifier) tags with php?
They would need to be in the format
4402bd8a-cd51-40ea-99d7-b510e89e344b
Specifically this is for adding accessibility for the PicLens browser
add-on (which uses an .rss file) to some of our photo gallery pages. It
seems to require each thumbnail have an identical GUID tag in both the
html and the rss file.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |