Re: Calling all math wizards

["rick baird" <rick.baird@xxxxxxxxx>]

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.