For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
hi I have a query where in I have used 'not in ' construct It takes a lot of time .. any other way to get the result faster? e.g. select custno from cust_mast where custno not in (select custno from other_mast) please help
Hi,
For the same COL1 and COL2/COL3 , check the select different values from COL4 For instance for COL1=600 , COL2=140/COL3=2 and COL2=150/COL3=3 Return 20 and 35 as not common values
For the same COL1 and COL2/COL3 , check the select different values from COL4
For instance for COL1=600 , COL2=140/COL3=2 and COL2=150/COL3=3
Return 20 and 35 as not common values
I didn't get you on this logic, how did you selected 20 and 30, can you elaborate
Update : are you looking for this , just give insert into table and this query
SQL> with tab1 as(select '600' col1, '140' col2, '2' col3, '10' col4,'1300' col5,'500' col6,'1' col7 from dual union allselect '600' col1, '140' col2, '2' col3, '20' col4,'1400' col5,'340' col6,'1' col7 from dual union allselect '600' col1, '140' col2, '2' col3, '10' col4,'1400' col5,'230' col6,'1' col7 from dual union allselect '600' col1, '140' col2, '2' col3, '35' col4,'1700' col5,'120' col6,'1' col7 from dual union allselect '600' col1, '150' col2, '3' col3, '10' col4,'1300' col5,'166' col6,'1' col7 from dual union allselect '600' col1, '150' col2, '3' col3, '15' col4,'1400' col5,'435' col6,'1' col7 from dual), grp1 as (select *from tab1where col1 = 600and col2=140), grp2 as (select *from tab1where col1 = 600and col2=150)select distinct res.*from grp1 res , (select p1.*from grp1 p1, grp2 p2where p1.col1 = p2.col1and p1.col4 = p2.col4) exwhere res.col1 = ex.col1and res.col2 = ex.col2and res.col4 <> ex.col4 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 ; COL COL C CO COL5 COL C--- --- - -- ---- --- -600 140 2 20 1400 340 1600 140 2 35 1700 120 1
SQL> with tab1 as(select '600' col1, '140' col2, '2' col3, '10' col4,'1300' col5,'500' col6,'1' col7 from dual union allselect '600' col1, '140' col2, '2' col3, '20' col4,'1400' col5,'340' col6,'1' col7 from dual union allselect '600' col1, '140' col2, '2' col3, '10' col4,'1400' col5,'230' col6,'1' col7 from dual union allselect '600' col1, '140' col2, '2' col3, '35' col4,'1700' col5,'120' col6,'1' col7 from dual union allselect '600' col1, '150' col2, '3' col3, '10' col4,'1300' col5,'166' col6,'1' col7 from dual union allselect '600' col1, '150' col2, '3' col3, '15' col4,'1400' col5,'435' col6,'1' col7 from dual), grp1 as (select *from tab1where col1 = 600and col2=140), grp2 as (select *from tab1where col1 = 600and col2=150)select distinct res.*from grp1 res , (select p1.*from grp1 p1, grp2 p2where p1.col1 = p2.col1and p1.col4 = p2.col4) exwhere res.col1 = ex.col1and res.col2 = ex.col2and res.col4 <> ex.col4 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 ;
COL COL C CO COL5 COL C--- --- - -- ---- --- -600 140 2 20 1400 340 1600 140 2 35 1700 120 1
- Pavan Kumar N
No need for procedure. Use, for example, full outer join:
with t1 as (
select *
from table1
where col1 = 600
and col2 = 140
and col3 = 2
),
t2 as (
and col2 = 150
and col3 = 3
)
select nvl(t1.col4,t2.col4) col4
from t1
full join
t2
on t2.col4 = t1.col4
where t1.col4 is null
or t2.col4 is null
/
COL4---------- 20 35
SQL>
SY.
or may be like this?
select col1, col4
group by col1, col4
having count(distinct col2)=1 and count(distinct col3)=1;
@Pavan Kumar
I mean for source combination 600 , 140, 2 and destination combination 600, 150, 3 check the values of col4.
And bring me the col1, col2 , col3 from destination col4 from source that does not exist in destination , col5 , col6 from souce , and col7 from a sequence.
So, I want to insert on existing table tab1 2 rows :
600 , 150 , 3, 20 , 1400 , 340, 7
600 , 150 , 3, 35 , 1700 , 120, 8
not
600 140 2 20 1400 340 1
600 140 2 35 1700 120 1
-----------------------
@Solomon Yakobson
I need procedure because I will use it in adf (parameters will be bindings)
But this you answered to me I can do it only with minus. I have difficulty to extract all the values I need.
And what prevents you from useing bind variables in SQL:
where col1 = :P_COL1
and col2 = :P_FROM_COL2
and col3 = :P_FROM_COL3
and col2 = :P_TO_COL2
and col3 = :P_TO_COL3
P.S. Btw, by looking at your code all you need is MERGE statement.
I don't know how to use merge ..
Is it correct something like the following?
select :P_COL1
,:P_FROM_COL2
,:P_FROM_COL3
,a.col4
,a.col5
,a.col6
from :table_name as a
left outer join :table_name as b
on b.col1=a.col1
and b.col2=:P_TO_COL2
and b.col3=:P_TO_COL3
and b.col4=a.col4
where a.col1=:P_COL1
and a.col2=:P_from_COL2
and a.col3=:P_from_col3
and b.col1 is null;
I think Could I use the code in a function like do_copy returning a temp table and in procedure just write
insert into table1 select * from do_copy(600,140,2,150,3,'table1');
Sorry, even though MERGE can be used it wouldn't be right tool here. All you need is INSERT. The following replaces your procedure:
SQL> select * 2 from table1 3 /
COL1 COL2 COL3 COL4 COL5 COL6 COL7---------- ---------- ---------- ---------- ---------- ---------- ---------- 600 140 2 10 1300 500 1 600 140 2 20 1400 340 4 600 140 2 15 1400 230 3 600 140 2 35 1700 120 2 600 150 3 10 1300 166 6 600 150 3 15 1400 435 5
6 rows selected.
SQL> insert 2 into table1 3 select :P_COL1, 4 :P_TO_COL2, 5 :P_TO_COL3, 6 col4, 7 null, 8 null, 9 null 10 from table1 11 where col1 = :P_COL1 12 and col2 = :P_FROM_COL2 13 and col3 = :P_FROM_COL3 14 and :P_FLG1 = 'Y' 15 minus 16 select :P_COL1, 17 :P_TO_COL2, 18 :P_TO_COL3, 19 col4, 20 null, 21 null, 22 null 23 from table1 24 where col1 = :P_COL1 25 and col2 = :P_TO_COL2 26 and col3 = :P_TO_COL3 27 and :P_FLG1 = 'Y' 28 /
2 rows created.
COL1 COL2 COL3 COL4 COL5 COL6 COL7---------- ---------- ---------- ---------- ---------- ---------- ---------- 600 140 2 10 1300 500 1 600 140 2 20 1400 340 4 600 140 2 15 1400 230 3 600 140 2 35 1700 120 2 600 150 3 10 1300 166 6 600 150 3 15 1400 435 5 600 150 3 20 600 150 3 35
8 rows selected.
And if you want to insert missing COL4 values both ways, use full join, as I already showed (slightly modified):
SQL> insert 2 into table1 3 values(600,150,3,70,1500,567,8) 4 /
1 row created.
SQL> insert 2 into table1 3 values(600,150,3,90,1900,789,9) 4 /
COL1 COL2 COL3 COL4 COL5 COL6 COL7---------- ---------- ---------- ---------- ---------- ---------- ---------- 600 140 2 10 1300 500 1 600 140 2 20 1400 340 4 600 140 2 15 1400 230 3 600 140 2 35 1700 120 2 600 150 3 10 1300 166 6 600 150 3 15 1400 435 5 600 150 3 70 1500 567 8 600 150 3 90 1900 789 9
SQL> insert 2 into table1 3 with t1 as ( 4 select * 5 from table1 6 where col1 = :P_COL1 7 and col2 = :P_FROM_COL2 8 and col3 = :P_FROM_COL3 9 and :P_FLG1 = 'Y' 10 ), 11 t2 as ( 12 select * 13 from table1 14 where col1 = :P_COL1 15 and col2 = :P_TO_COL2 16 and col3 = :P_TO_COL3 17 and :P_FLG1 = 'Y' 18 ) 19 select :P_COL1 col1, 20 nvl2(t1.col4,:P_TO_COL2,:P_FROM_COL2) col2, 21 nvl2(t1.col4,:P_TO_COL3,:P_FROM_COL3) col3, 22 nvl(t1.col4,t2.col4) col4, 23 null, 24 null, 25 null 26 from t1 27 full join 28 t2 29 on t2.col4 = t1.col4 30 where t1.col4 is null 31 or t2.col4 is null 32 /
4 rows created.
COL1 COL2 COL3 COL4 COL5 COL6 COL7---------- ---------- ---------- ---------- ---------- ---------- ---------- 600 140 2 10 1300 500 1 600 140 2 20 1400 340 4 600 140 2 15 1400 230 3 600 140 2 35 1700 120 2 600 150 3 10 1300 166 6 600 150 3 15 1400 435 5 600 150 3 70 1500 567 8 600 150 3 90 1900 789 9 600 140 2 70 600 140 2 90 600 150 3 35
COL1 COL2 COL3 COL4 COL5 COL6 COL7---------- ---------- ---------- ---------- ---------- ---------- ---------- 600 150 3 20
12 rows selected.
Thanks a lot for your help! It works great!