DECLARE
ln_cnt number;
lc_sql varchar2(32767);
lc_name varchar2(100) := 'tmp_table_test';
BEGIN
-- check exists table
begin
lc_name := dbms_assert.sql_object_name(lc_name);
exception when others then
--create table if not exists
lc_sql := '
create table tmp_table_test as
select rownum as rid,
1 as operator_id,
cast(null as number) as route_id
from dual t
connect by level <= 1000';
execute immediate lc_sql;
end;
lc_sql := '
with w_table1 as
(
select t.rid,
t.operator_id,
t.route_id,
trunc(dbms_random.value, 2) as ratio
from tmp_table_test t
),
w_table2 as
(
select 1 as operator_id,
level as route_id,
case when mod(level,2) = 0 then 0 else 0.5 end as ratio_from,
case when mod(level,2) = 0 then 0.4999 else 1 end as ratio_to,
case when level > 2 then 1 else 2 end gate
from dual
connect by level <= 4
)
select count(1) cnt
into :ln_cnt
from
(
select v.rid,
v.operator_id,
s.gate,
s.route_id,
row_number() over(partition by v.rid order by v.operator_id) rn
from w_table1 v
inner join w_table2 s
on v.ratio between s.ratio_from and s.ratio_to
AND (S.OPERATOR_ID = V.OPERATOR_ID OR V.ROUTE_ID IS NOT NULL) --!!!!!!!
--AND (S.OPERATOR_ID = V.OPERATOR_ID OR 1=0) --!!!!!!!
--AND S.OPERATOR_ID = V.OPERATOR_ID --this OK
) tb
where tb.rn=1
';
execute immediate lc_sql INTO ln_cnt;
dbms_output.put_line(ln_cnt);
END;Table created.
933
DECLARE
ln_cnt number;
lc_sql varchar2(32767);
lc_name varchar2(100) := 'tmp_table_test';
BEGIN
-- check exists table
begin
lc_name := dbms_assert.sql_object_name(lc_name);
exception when others then
--create table if not exists
lc_sql := '
create table tmp_table_test as
select rownum as rid,
1 as operator_id,
cast(null as number) as route_id
from dual t
connect by level <= 1000';
execute immediate lc_sql;
end;
lc_sql := '
with w_table1 as
(
select t.rid,
t.operator_id,
t.route_id,
trunc(dbms_random.value, 2) as ratio
from tmp_table_test t
),
w_table2 as
(
select 1 as operator_id,
level as route_id,
case when mod(level,2) = 0 then 0 else 0.5 end as ratio_from,
case when mod(level,2) = 0 then 0.4999 else 1 end as ratio_to,
case when level > 2 then 1 else 2 end gate
from dual
connect by level <= 4
)
select count(1) cnt
into :ln_cnt
from
(
select v.rid,
v.operator_id,
s.gate,
s.route_id,
row_number() over(partition by v.rid order by v.operator_id) rn
from w_table1 v
inner join w_table2 s
on v.ratio between s.ratio_from and s.ratio_to
AND (S.OPERATOR_ID = V.OPERATOR_ID OR V.ROUTE_ID IS NOT NULL) --!!!!!!!
--AND (S.OPERATOR_ID = V.OPERATOR_ID OR 1=0) --!!!!!!!
--AND S.OPERATOR_ID = V.OPERATOR_ID --this OK
) tb
where tb.rn=1
';
execute immediate lc_sql INTO ln_cnt;
dbms_output.put_line(ln_cnt);
END;Table created.
939
DECLARE
ln_cnt number;
lc_sql varchar2(32767);
lc_name varchar2(100) := 'tmp_table_test';
BEGIN
-- check exists table
begin
lc_name := dbms_assert.sql_object_name(lc_name);
exception when others then
--create table if not exists
lc_sql := '
create table tmp_table_test as
select rownum as rid,
1 as operator_id,
cast(null as number) as route_id
from dual t
connect by level <= 1000';
execute immediate lc_sql;
end;
lc_sql := '
with w_table1 as
(
select t.rid,
t.operator_id,
t.route_id,
trunc(dbms_random.value, 2) as ratio
from tmp_table_test t
),
w_table2 as
(
select 1 as operator_id,
level as route_id,
case when mod(level,2) = 0 then 0 else 0.5 end as ratio_from,
case when mod(level,2) = 0 then 0.4999 else 1 end as ratio_to,
case when level > 2 then 1 else 2 end gate
from dual
connect by level <= 4
)
select count(1) cnt
into :ln_cnt
from
(
select v.rid,
v.operator_id,
s.gate,
s.route_id,
row_number() over(partition by v.rid order by v.operator_id) rn
from w_table1 v
inner join w_table2 s
on v.ratio between s.ratio_from and s.ratio_to
--AND (S.OPERATOR_ID = V.OPERATOR_ID OR V.ROUTE_ID IS NOT NULL) --!!!!!!!
AND (S.OPERATOR_ID = V.OPERATOR_ID OR 1!=0) --!!!!!!!
--AND S.OPERATOR_ID = V.OPERATOR_ID --this OK
) tb
where tb.rn=1
';
execute immediate lc_sql INTO ln_cnt;
dbms_output.put_line(ln_cnt);
END;Table created.
932