SQL & PL/SQL

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
        35

SQL>

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          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.

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        35

8 rows selected.

SQL>

SY.

Thanks a lot for your help! It works great!