Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    17

    Unanswered: Assigning a RANDOM value

    Hi Guys,

    Im not entirely sure if this is possible, but if it is you guys will probably know.
    I have a CASE statement that in words should go
    IF MACHINE1 IS BUSY THEN SELECT ONE OF THE ALTERNATIVE MACHINES - IT DOESNT MATTER WHICH.

    CASE WHEN MAC1= 'I01' THEN ...

    This is where Im stuck, how can I give Oracle a list of acceptable alternatives (hardcoded) and ask it to pick one at random?
    I thought about the random function but I dont know how this could be applied.

    If you have any ideas I would be very grateful - this has us stumped. We are bringing this over from a CRYSTAL REPORT which allowed us to write it as

    IF mac1 = 'I01' Then Mac_Used IN ('Mac2', 'Mac3', 'Mac4', 'Mac5')

    I have never seen this type of statement in Oracle.

    Thanks,

    Gerard.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    This will randomly return 1, 2, 3 or 4:
    Code:
    ceil(dbms_random.value*4)
    So you could do:
    Code:
    CASE ceil(dbms_random.value*4)
       WHEN 1 THEN 'Mac2'
       WHEN 2 THEN 'Mac3'
       WHEN 3 THEN 'Mac4'
       WHEN 4 THEN 'Mac5'
       END

  3. #3
    Join Date
    Jul 2004
    Posts
    17
    Quote Originally Posted by andrewst
    This will randomly return 1, 2, 3 or 4:
    Code:
    ceil(dbms_random.value*4)
    So you could do:
    Code:
    CASE ceil(dbms_random.value*4)
       WHEN 1 THEN 'Mac2'
       WHEN 2 THEN 'Mac3'
       WHEN 3 THEN 'Mac4'
       WHEN 4 THEN 'Mac5'
       END

    Thanks for your reply.
    This is very close to what Im after but Im not sure whether it gets me there. I know what Im testing in the case and criteria here is specific. Im sorry I realise my example was a poor one now..

    CASE WHEN MACHINE1 = 'BUSY' THEN ANSWER CAN BE ONE OF (2,3,4)
    WHEN MACHINE2 = BUSY THEN ANSWER CAN BE ONE OF (3,5,6)
    WHEN MACHINE3 = BUSY THEN ANSWER CAN BE ONE OF (2,4,5,6,7)

    I dont mind which answer it picks as long as its in the list provided.

    Again, apologies for not explaining myself clearly in the beginning

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I'd probably write a PL/SQL function for that:
    Code:
    FUNCTION free_machine RETURN NUMBER IS
       l_machine NUMBER;
    BEGIN
       l_machine := 1;
       IF busy(l_machine) THEN
          l_machine := CASE ceil(dbms_random.value*3)
                          WHEN 1 THEN 2
                          WHEN 2 THEN 3
                          WHEN 3 THEN 4
                          END;
          IF busy(l_machine) THEN
             l_machine := CASE ceil(dbms_random.value*3)
                             WHEN 1 THEN 3
                             WHEN 2 THEN 5
                             WHEN 3 THEN 6
                             END;
             IF busy(l_machine) THEN
                l_machine := CASE ceil(dbms_random.value*5)
                                WHEN 1 THEN 2
                                WHEN 2 THEN 4
                                WHEN 3 THEN 5
                                WHEN 4 THEN 6
                                WHEN 5 THEN 7
                                END;
             END IF;
          END IF;
       END IF;
       RETURN l_machine;
    END;
    busy(l_machine) is a call to another function that determines whether the machine is 'BUSY' or not. You may have a different way of doing that.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •