Home » SQL & PL/SQL » SQL & PL/SQL » Interesting MINUS problem (merged) (Oracle 10G)
Interesting MINUS problem (merged) [message #428806] Fri, 30 October 2009 02:46 Go to next message
zeratul1979
Messages: 4
Registered: October 2009
Junior Member
Hi all,

i have interesting problem that i cannot sort out with MINUS
i need to get difference between two views as in example below

View1 (They key is automatically generated and not shown)
-----
Purchase Nr Line Release Qty
2881 1 1 0.1
2881 1 1 0.1
3001 1 1 0.5

View2
-----
2881 1 1 0.1


When i MINUS these two i get one result
3001 1 1 0.5

The challenge is thus:
I want it to show one of the occurences from View1 as well, therefore only cancelling out one line that is matched between two views and must give me
2881 1 1 0.1
3001 1 1 0.5

Possible??
Re: Interesting MINUS problem (merged) [message #428810 is a reply to message #428806] Fri, 30 October 2009 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
MINUS is a SET operator.
SET does NOT contain duplicates.
So a DISTINCT is first done.
And so the result is correct.

Regards
Michel

[Updated on: Fri, 30 October 2009 03:03]

Report message to a moderator

Re: Interesting MINUS problem (merged) [message #428812 is a reply to message #428806] Fri, 30 October 2009 02:55 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
zeratul1979 wrote on Fri, 30 October 2009 02:46



When i MINUS these two i get one result
3001 1 1 0.5


Possible??




What you have done so far?

There is nothing challenging here

[Updated on: Fri, 30 October 2009 02:57]

Report message to a moderator

Re: Interesting MINUS problem (merged) [message #428814 is a reply to message #428806] Fri, 30 October 2009 03:00 Go to previous messageGo to next message
zeratul1979
Messages: 4
Registered: October 2009
Junior Member
YES i know Minus wont work as it is SET operator. so i am not wrong. read the question again please
I was asking if it is possible to check differences between two views in looking at rows. 
Re: Interesting MINUS problem (merged) [message #428815 is a reply to message #428810] Fri, 30 October 2009 03:02 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Sorry! It was a mistake in understanding Smile

SQL> select * from t;

         A B
---------- -----
         1 a
         2 b
         1 c
         1 a

SQL> select * from t1;

         A B
---------- -----
         1 a

SQL> select * from t 
     minus
    select * from t1 

         A B
---------- -----
         1 c
         2 b

SQL> 

[Updated on: Fri, 30 October 2009 03:04]

Report message to a moderator

Re: Interesting MINUS problem (merged) [message #428817 is a reply to message #428814] Fri, 30 October 2009 03:04 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
zeratul1979 wrote on Fri, 30 October 2009 09:00
YES i know Minus wont work as it is SET operator. so i am not wrong. read the question again please
I was asking if it is possible to check differences between two views in looking at rows. 

Yes it is possible.

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel

Re: Interesting MINUS problem (merged) [message #428824 is a reply to message #428806] Fri, 30 October 2009 03:57 Go to previous messageGo to next message
zeratul1979
Messages: 4
Registered: October 2009
Junior Member
ok here is Test Case for my problem

create table example_a
(
id number not null,
po number not null,
line_no number not null,
quantity  number not null)
/

alter table example_a add primary key (id)
/

insert into example_a values (1001,8331,1,0.1);
insert into example_a values (1002,8331,1,0.1);
insert into example_a values (1003,8441,1,0.5);

/
create table example_b
(
id number not null,
po number not null,
line_no number not null,
quantity  number not null)
/

alter table example_b add primary key (id)
/

insert into example_b values (1111,8331,1,0.1);

commit
/



SQL> select po, line_no, quantity from example_a
  2  MINUS
  3  select po, line_no, quantity from example_b
  4  /
 
        PO    LINE_NO   QUANTITY
---------- ---------- ----------
      8441          1        0.5






What i would rather want is something else that is not MINUS that will give me result. Therefor comparing each line at it's own right and not UNIQUE
        PO    LINE_NO   QUANTITY
---------- ---------- ----------
      8331          1        0.1
      8441          1        0.5
Re: Interesting MINUS problem (merged) [message #428827 is a reply to message #428824] Fri, 30 October 2009 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with
  2    data1 as (select po, line_no, quantity, count(*) nb 
  3              from example_a group by po, line_no, quantity),
  4    data2 as (select po, line_no, quantity, count(*) nb 
  5              from example_b group by po, line_no, quantity),
  6    lines as (select level line from dual connect by level <= 10)
  7  select d1.po, d1.line_no, d1.quantity
  8  from data1 d1, data2 d2, lines
  9  where d2.po (+) = d1.po
 10    and d2.line_no (+) = d1.line_no
 11    and d2.quantity (+) = d1.quantity
 12    and line <= d1.nb-nvl(d2.nb,0)
 13  /

        PO    LINE_NO   QUANTITY
---------- ---------- ----------
      8331          1         .1
      8441          1         .5

2 rows selected.

Regards
Michel
Re: Interesting MINUS problem (merged) [message #428829 is a reply to message #428827] Fri, 30 October 2009 04:35 Go to previous messageGo to next message
zeratul1979
Messages: 4
Registered: October 2009
Junior Member
very eligantly done Michel Smile

merci beaucoup !!!
Re: Interesting MINUS problem (merged) [message #428830 is a reply to message #428827] Fri, 30 October 2009 04:46 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Why didn't you just use UNION operator? It would have given same result or May be I did not understand the question correctly

[update] oops . sorry I did not notice that values in ID field were different...

but this query would've given same result for the test case OP posted

select po, line_no, quantity from example_a
    union
select po, line_no, quantity from example_b


[Updated on: Fri, 30 October 2009 04:54]

Report message to a moderator

Re: Interesting MINUS problem (merged) [message #428836 is a reply to message #428830] Fri, 30 October 2009 05:13 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"union" is quite different than "minus".
Just add a row in second table that does not exist in first one.

It is also wrong if you duplicate the rows in first table (changing the id).

Regards
Michel
Re: Interesting MINUS problem (merged) [message #428877 is a reply to message #428836] Fri, 30 October 2009 08:02 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Michel Cadot wrote on Fri, 30 October 2009 15:43
Just add a row in second table that does not exist in first one.


Yep, completely missed that point on for "Union" operator
Re: Interesting MINUS problem (merged) [message #428878 is a reply to message #428824] Fri, 30 October 2009 08:11 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
SQL> select po, line_no, quantity
  2  from
  3  (
  4  select po, line_no, quantity,row_number() OVER (PARTITION BY po ORDER BY line_no)
  5  from example_a
  6  MINUS
  7  select po, line_no, quantity,row_number() OVER (PARTITION BY po ORDER BY line_no)
  8   from example_b
  9   )
 10  /

        PO    LINE_NO   QUANTITY
---------- ---------- ----------
      8331          1         .1
      8441          1         .5

[Updated on: Fri, 30 October 2009 08:14]

Report message to a moderator

Re: Interesting MINUS problem (merged) [message #428882 is a reply to message #428878] Fri, 30 October 2009 08:25 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You were lucky with these data otherwise your query didn't (seem to) work.

Regards
Michel

[Updated on: Fri, 30 October 2009 08:28]

Report message to a moderator

Re: Interesting MINUS problem (merged) [message #428883 is a reply to message #428878] Fri, 30 October 2009 08:30 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Fixed query:
SQL> select po, line_no, quantity
  2  from (
  3    select po, line_no, quantity,
  4           row_number() over (partition by po, line_no, quantity order by null)
  5    from example_a
  6    minus
  7    select po, line_no, quantity,
  8           row_number() over (partition by po, line_no, quantity order by null)
  9    from example_b
 10    )
 11  /
        PO    LINE_NO   QUANTITY
---------- ---------- ----------
      8331          1         .1
      8441          1         .5

2 rows selected.

Regards
Michel
Re: Interesting MINUS problem (merged) [message #429055 is a reply to message #428883] Sun, 01 November 2009 22:35 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Great!!
Previous Topic: different on upate in (oracle and sqlserver)
Next Topic: Unable to compile
Goto Forum:
  


Current Time: Fri Sep 30 05:29:07 CDT 2016

Total time taken to generate the page: 0.09620 seconds