Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.8K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 395 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
Retrieving data before insert statement in procedure
Hi,
I am new in plsql programming and I would like to make a procedure.I have tables like the following TABLE1
|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 |
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
Then insert in this table TABLE1 the rows
600 , 150 , 3, 20 , 1400 , 340, 7
600 , 150 , 3, 35 , 1700 , 120, 8
I am trying to make the procedure like below but I have problem how to retrieve data in insert statement
PROCEDURE COPY_COLUMNS ( P_COL1 IN A.COL1%TYPE, P_FROM_COL2 IN B.COL2%TYPE, P_FROM_COL3 IN B.COL3%TYPE, P_TO_COL2 IN B.COL2%TYPE, P_TO_COL3 IN B.COL3%TYPE, P_FLG1 IN VARCHAR2, P_FLG2 IN VARCHAR2, P_FLG3 IN VARCHAR2 ) IS CURSOR CFL1 IS select COL4 FROM TABLE1 WHERE COL1 = P_COL1 AND COL2 = P_FROM_COL2 AND COL3 = P_FROM_COL3 MINUS select COL4 FROM TABLE1 WHERE COL1 = P_COL1 AND COL2 = P_TO_COL2 AND COL3 = P_TO_COL3; CURSOR CFL2 IS select COL4 FROM TABLE2 WHERE COL1 = P_COL1 AND COL2 = P_FROM_COL2 AND COL3 = P_FROM_COL3 MINUS select COL4 FROM TABLE2 WHERE COL1 = P_COL1 AND COL2 = P_TO_COL2 AND COL3 = P_TO_COL3; CURSOR CFL3 IS select COL4 FROM TABLE3 WHERE COL1 = P_COL1 AND COL2 = P_FROM_COL2 AND COL3 = P_FROM_COL3 MINUS select COL4 FROM TABLE3 WHERE COL1 = P_COL1 AND COL2 = P_TO_COL2 AND COL3 = P_TO_COL3; V_REC CFL1%ROWTYPE; BEGIN IF P_FLG1='N' OR P_FLG2='N' OR P_FLG3='N' THEN GOTO label; --do nothing END IF; IF P_FLG1 = 'Y' THEN OPEN CFL1; FETCH CFL1 INTO V_REC; CLOSE C1; -- SELECT COL5, COL6 -- FROM TABLE1 -- WHERE COL1 = P_COL1 AND COL2 = P_FROM_COL2 AND COL3 = P_FROM_COL3 AND COL4 = V_REC.COL4; FOR REC IN CFL1 LOOP INSERT INTO TABLE1 SELECT P_COL1, P_TO_COL2, P_TO_COL3, CFL1.COL4, -- COL5 ?? , COL6 ?? -- , SEQname2.NEXTVAL) END LOOP; END IF; <<label>> END;
Could you help me please do it ?
Thanks in advance
Best Answer
-
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 /1 row created.
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
600 150 3 70 1500 567 8
600 150 3 90 1900 789 98 rows selected.
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.
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
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 35COL1 COL2 COL3 COL4 COL5 COL6 COL7
---------- ---------- ---------- ---------- ---------- ---------- ----------
600 150 3 2012 rows selected.
SQL>
SY.
Answers
-
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
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 all
select '600' col1, '140' col2, '2' col3, '20' col4,'1400' col5,'340' col6,'1' col7 from dual union all
select '600' col1, '140' col2, '2' col3, '10' col4,'1400' col5,'230' col6,'1' col7 from dual union all
select '600' col1, '140' col2, '2' col3, '35' col4,'1700' col5,'120' col6,'1' col7 from dual union all
select '600' col1, '150' col2, '3' col3, '10' col4,'1300' col5,'166' col6,'1' col7 from dual union all
select '600' col1, '150' col2, '3' col3, '15' col4,'1400' col5,'435' col6,'1' col7 from dual
)
, grp1 as (
select *
from tab1
where col1 = 600
and col2=140
)
, grp2 as (
select *
from tab1
where col1 = 600
and col2=150
)
select distinct res.*
from grp1 res , (
select p1.*
from grp1 p1, grp2 p2
where p1.col1 = p2.col1
and p1.col4 = p2.col4
) ex
where res.col1 = ex.col1
and res.col2 = ex.col2
and 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 1
600 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 (
select *
from table1
where col1 = 600
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
35SQL>
SY. -
or may be like this?
select col1, col4
from table1
group by col1, col4
having count(distinct col2)=1 and count(distinct col3)=1;
-
@Pavan Kumar
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 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:
with t1 as (select *
from table1
where col1 = :P_COL1
and col2 = :P_FROM_COL2
and col3 = :P_FROM_COL3
),
t2 as (
select *
from table1
where col1 = :P_COL1
and col2 = :P_TO_COL2
and col3 = :P_TO_COL3
)
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
/
SY.
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 56 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.
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
600 150 3 20
600 150 3 358 rows selected.
SQL>
SY. -
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 /1 row created.
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
600 150 3 70 1500 567 8
600 150 3 90 1900 789 98 rows selected.
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.
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
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 35COL1 COL2 COL3 COL4 COL5 COL6 COL7
---------- ---------- ---------- ---------- ---------- ---------- ----------
600 150 3 2012 rows selected.
SQL>
SY.
-
Thanks a lot for your help! It works great!