увы, повторяется и на 12, 18, но исправлено в 19, так что ищите баг/запрашивайте фикс на My Oracle Support
А вот у меня не повторяется.
12C:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE 12.2.0.1.0 Production
TNS for 64-bit Windows: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production
SQL> create table a as
2 (select 3413 aid, 'С01' acod, null aref from dual
3 union all
4 select 5493 aid, 'У30' acod, 3413 aref from dual
5 union all
6 select 5494 aid, '30' acod, 5493 aref from dual
7 union all
8 select 5743 aid, '3007' acod, 5494 aref from dual
9 union all
10 select 5744 aid, '300701' acod, 5743 aref from dual
11 )
12 /
Table created.
SQL> create table b as (
2 select 7 id, '30' bcod from dual
3 union all
4 select 37 id, '3007' bcod from dual
5 )
6 /
Table created.
SQL> alter table b modify id not null;
Table altered.
SQL>
SQL> select aid, aref, acod, bcod, id, nvl2(id,1,0)
2 from (
3 select aid, aref, acod, bcod, id
4 from a, b
5 where acod= bcod(+)
6 start with aid= 5744
7 connect by prior aref= aid
8 )
9 where id is not null;
AID AREF ACOD BCOD ID NVL2(ID,1,0)
---------- ---------- ------ ---- ---------- ------------
5743 5494 3007 3007 37 1
5494 5493 30 30 7 1
SQL> select aid, aref, acod, bcod, id, nvl2(id,1,0)
2 from (
3 select
4 aid, aref, acod, bcod, id
5 from a, b
6 where acod= bcod(+)
7 start with aid= 5744
8 connect by prior aref= aid
9 )
10 where id+0 is not null;
AID AREF ACOD BCOD ID NVL2(ID,1,0)
---------- ---------- ------ ---- ---------- ------------
5743 5494 3007 3007 37 1
5494 5493 30 30 7 1
SQL>
18C:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
SQL> create table a as
2 (select 3413 aid, 'С01' acod, null aref from dual
3 union all
4 select 5493 aid, 'У30' acod, 3413 aref from dual
5 union all
6 select 5494 aid, '30' acod, 5493 aref from dual
7 union all
8 select 5743 aid, '3007' acod, 5494 aref from dual
9 union all
10 select 5744 aid, '300701' acod, 5743 aref from dual
11 )
12 /
Table created.
SQL> create table b as (
2 select 7 id, '30' bcod from dual
3 union all
4 select 37 id, '3007' bcod from dual
5 )
6 /
Table created.
SQL> alter table b modify id not null;
Table altered.
SQL>
SQL> select aid, aref, acod, bcod, id, nvl2(id,1,0)
2 from (
3 select aid, aref, acod, bcod, id
4 from a, b
5 where acod= bcod(+)
6 start with aid= 5744
7 connect by prior aref= aid
8 )
9 where id is not null;
AID AREF ACOD BCOD ID NVL2(ID,1,0)
---------- ---------- ------ ---- ---------- ------------
5743 5494 3007 3007 37 1
5494 5493 30 30 7 1
SQL> select aid, aref, acod, bcod, id, nvl2(id,1,0)
2 from (
3 select
4 aid, aref, acod, bcod, id
5 from a, b
6 where acod= bcod(+)
7 start with aid= 5744
8 connect by prior aref= aid
9 )
10 where id+0 is not null;
AID AREF ACOD BCOD ID NVL2(ID,1,0)
---------- ---------- ------ ---- ---------- ------------
5743 5494 3007 3007 37 1
5494 5493 30 30 7 1
SQL>
19C:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> create table a as
2 (select 3413 aid, 'С01' acod, null aref from dual
3 union all
4 select 5493 aid, 'У30' acod, 3413 aref from dual
5 union all
6 select 5494 aid, '30' acod, 5493 aref from dual
7 union all
8 select 5743 aid, '3007' acod, 5494 aref from dual
9 union all
10 select 5744 aid, '300701' acod, 5743 aref from dual
11 )
12 /
Table created.
SQL> create table b as (
2 select 7 id, '30' bcod from dual
3 union all
4 select 37 id, '3007' bcod from dual
5 )
6 /
Table created.
SQL> alter table b modify id not null;
Table altered.
SQL>
SQL> select aid, aref, acod, bcod, id, nvl2(id,1,0)
2 from (
3 select aid, aref, acod, bcod, id
4 from a, b
5 where acod= bcod(+)
6 start with aid= 5744
7 connect by prior aref= aid
8 )
9 where id is not null;
AID AREF ACOD BCOD ID NVL2(ID,1,0)
---------- ---------- ------ ---- ---------- ------------
5743 5494 3007 3007 37 1
5494 5493 30 30 7 1
SQL> select aid, aref, acod, bcod, id, nvl2(id,1,0)
2 from (
3 select
4 aid, aref, acod, bcod, id
5 from a, b
6 where acod= bcod(+)
7 start with aid= 5744
8 connect by prior aref= aid
9 )
10 where id+0 is not null;
AID AREF ACOD BCOD ID NVL2(ID,1,0)
---------- ---------- ------ ---- ---------- ------------
5743 5494 3007 3007 37 1
5494 5493 30 30 7 1
SQL>
SY. |
