Hi,

Below both queries return 3 records. Could you please let me know why?

select * from hr.employees
where rownum in (select level from dual connect by level <= 5);

select * from hr.employees
where rownum in (select level from dual connect by level <= 7);

SELECT *
FROM
    (
        SELECT DISTINCT
            level "LEVEL"
        FROM
            "SYS"."DUAL" "DUAL"
        CONNECT BY
            level <= 5
    ) "VW_NSO_1",
    "HR"."EMPLOYEES" "EMPLOYEES"
WHERE
    "VW_NSO_1"."LEVEL" = ROWNUM;

SELECT DISTINCT
    level "LEVEL"
FROM
    "SYS"."DUAL" "DUAL"
CONNECT BY
    level <= 5;

LEVEL  
1      
2      
4      <====
5      <====
3

It gets weirder :

select * from sysp.employee
where rownum + X in (select level from dual connect by level <= 14);

X Rows
0 3
1 2
2 1
3 2
4 1
5 2
6 1
7 3
8 2
...

go fig :)

X is column from sysp.employee table and added that x values to rownum in where clause

Hi,

I got the given results by manually using the given X value

select * from sysp.employee where rownum + 0 in..
select * from sysp.employee where rownum + 1 in..
select * from sysp.employee where rownum + 2 in..
...

1. The properties of the table (emplyoee in this case) does not seem to matter. Just tried it with another. It just needs to contain some rows.
2. The resulting output is always the same row (repeated 1 to 3 times). Possibly the "first" in the segment?
3. The number of rows for each X is stable (across reruns, or different tables) although the sequence looks random, particulary the 3 at X=7
4. If X reaches the inner value (14 here) or greater the result is always 0 rows. Almost understandable ;)

Hi,

There was no script (I did it manually) but there is now :

create table test_table (ID NUMBER(2), text VARCHAR2(10));

insert into test_table values (1, 'T1');

insert into test_table values (2, 'T2');

insert into test_table values (3, 'T3');

insert into test_table values (4, 'T4');

commit;

-- some examples :
select * from test_table
where rownum + 0 in (select level from dual connect by level <= 14);

select * from test_table
where rownum + 3 in (select level from dual connect by level <= 14);

select * from test_table
where rownum + 7 in (select level from dual connect by level <= 14);

declare
vRows NUMBER;
begin
dbms_output.enable(null);
dbms_output.put_line('X |Rows');
for i IN 1..15 LOOP
select count(*) INTO vRows from test_table
where rownum + i in (select level from dual connect by level <= 14);
dbms_output.put_line(i || '|' || vRows);
END LOOP;
end;

Table dropped.
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
Commit complete.

ID TEXT
---------- ---------------
1 T1
1 T1
1 T1

3 rows selected.

ID TEXT
---------- ---------------
1 T1
1 T1

2 rows selected.

ID TEXT
---------- ---------------
1 T1
1 T1
1 T1

3 rows selected.

PL/SQL procedure successfully completed.

X |Rows
0|3
1|2
2|1
3|2
4|1
5|2
6|1
7|3
8|2
9|1
10|2
11|1
12|2
13|1
14|0
15|0