RE: Calling all math wizards

["Paul Nelson" <nelsonp@xxxxxxxxxxxxx>]

Thanks. I can create a logical over the filed into which I'm going to store
this "invented" number.

Paul Nelson
Cell 708-670-6978
Office 512-392-2577
nelsonp@xxxxxxxxxxxxx


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rick baird
Sent: Saturday, October 20, 2007 6:55 AM
To: Midrange Systems Technical Discussion
Subject: Re: Calling all math wizards

start here:

 SELECT cast((RAND() * 1000000) as dec(7,0))
   FROM SYSIBM/SYSDUMMY1

that gives you a seven digit random number, but it won't insure it
doesn't already exist in your employee table.

at some point you will have check the number against the file and loop
back to get another one if it already exists.

In RPG, something like this:

c               dou found = 0

c/exec sql
c+ SELECT cast((RAND() * 1000000) as dec(7,0))
c+   into :random
c+   FROM SYSIBM/SYSDUMMY1
c/end-exec

c/exec sql
c+ SELECT count(*)
c+   into :found
c+   from EMPMAST
c+   where EMPNO = :random
c/end-exec

c                enddo




On 10/20/07, Paul Nelson <nelsonp@xxxxxxxxxxxxx> wrote:
> A client has posed an interesting problem. His employee master file
contains
> a 2 digit company number, a 3 digit division number, and a 5 digit
employee
> number. He needs to have some math done to generate a unique 7 digit
number.
> I've thought about using random numbers, square roots, logarithms, etc.,
but
> this has to tie back into his application software, and the number has to
be
> sent out to a third party employee benefits administrator so duplicates
are
> not permitted.
>
> I could come up with something via RPG, but the client's desire is to be
> able to do this via SQL. Any thoughts?
>
> Paul Nelson
> Cell 708-670-6978
> Office 512-392-2577
> nelsonp@xxxxxxxxxxxxx
>
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.