Farid Imranov Farid Imranov - 20 days ago 7
SQL Question

Strange random behavior in where clause

I have a table like this:

Id | GroupId | Category
------------------------
1 | 101 | A
2 | 101 | B
3 | 101 | C
4 | 103 | B
5 | 103 | D
6 | 103 | A
........................


I need select one of the
GroupId
randomly. For this I have used the following
PL/SQL code block
:

declare v_group_count number;
v_group_id number;
begin
select count(distinct GroupId) into v_group_count from MyTable;
SELECT GroupId into v_group_id FROM
(
SELECT GroupId, ROWNUM RN FROM
(SELECT DISTINCT GroupId FROM MyTable)
)
WHERE RN=Round(dbms_random.value(1, v_group_count));
end;


Because I Rounded random value then it will be
integer value
and
WHERE RN=Round(dbms_random.value(1, v_group_count))
condition must return one row always. Generally it gives me one row as expected. But strangely sometimes it gives me no rows and sometimes it returns two rows. That's why it gives error in this section:

SELECT GroupId into v_group_id


Anyone knows the reason of that behaviour?

Answer

Round(dbms_random.value(1, v_group_count)) is being executed for every row, so every row might be selected or not

Comments