?
Solved

oracle minus query is not returning the exact number of records ???

Posted on 2015-01-09
14
Medium Priority
?
439 Views
Last Modified: 2015-01-13
I am using minus query. I am expecting to get around 486 records. Indeed, i am getting only 101 records.  Is there a best alternate for minus syntax in oracle.

select svc_pvr_pyr_nr from tcomgrp
where sls_grp_cd ='IS'
and sales_vew_cd ='S1709'
and dat_cx_tm_prd_nr = '85'

minus

select svc_pvr_pyr_nr from tcomgrp
where  sls_grp_cd ='IS'
and sales_vew_cd ='S1709'
and  dat_cx_tm_prd_nr = '0' 

Open in new window

0
Comment
Question by:chokka
14 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40541829
There are alternatives, but they would produce the same  result (by definition).

Provide a sample of data and the expected result from that sample , then we should be able to offer  a solution.
0
 
LVL 21

Expert Comment

by:flow01
ID: 40541977
The minus return the unique values of the first query , so probably you have double values in the first query.

I saw somewhere an example of  minus all , but that does not work in my version.

So you have to create unique rows first and do the minus afterwards (if you need the doubles)
try

select svc_pvr_pyr_nr
from
(
select svc_pvr_pyr_nr ,  row_number() OVER (PARTITION BY svc_pvr_pyr_nr ORDER BY svc_pvr_pyr_nr) rn
from tcomgrp
where sls_grp_cd ='IS'
and sales_vew_cd ='S1709'
and dat_cx_tm_prd_nr = '85'
minus
select svc_pvr_pyr_nr ,  row_number() OVER (PARTITION BY svc_pvr_pyr_nr ORDER BY svc_pvr_pyr_nr) rn
from tcomgrp
where  sls_grp_cd ='IS'
and sales_vew_cd ='S1709'
and  dat_cx_tm_prd_nr = '0'
)
0
 

Author Comment

by:chokka
ID: 40542147
@flow01, Thank you. Your query makes sense. I will try and update you.
Yes, That table tcomgrp has duplicate - svc_pvr_pyr_nr

I tried using distinct(svc_pvr_pyr_nr ), i got the same 101 results. So i decided to post in EE.

I will try with your syntax and get back to you !!
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:chokka
ID: 40542323
On using CTE, minus query is throwing syntax exception. I tried by using braces on both the queries . Still i have syntax exception.



with cte 
as
(
select svc_pvr_pyr_nr ,  row_number() OVER (PARTITION BY svc_pvr_pyr_nr ORDER BY svc_pvr_pyr_nr) rn from A55601.TCOMGRP tcg
where sls_grp_cd ='IS'
 and sales_vew_cd ='S1709'
and dat_cx_tm_prd_nr = '96'
)
select cte.svc_pvr_pyr_nr from cte
where cte.rn = 1

minus

with cte 
as
(
select svc_pvr_pyr_nr ,  row_number() OVER (PARTITION BY svc_pvr_pyr_nr ORDER BY svc_pvr_pyr_nr) rn from A55601.TCOMGRP tcg
where sls_grp_cd ='IS'
 and sales_vew_cd ='S1709'
and dat_cx_tm_prd_nr = '0'
)
select cte.svc_pvr_pyr_nr from cte
where cte.rn = 1

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40542412
You cannot use with more than once in a single query, and must be at the top of the query. You can have more than one aliased query. like this:

with
  alias1 as (select 1 from dual)
, alias2 as (select 2 from dual)
select *
from alias1
cross join alias2

-------------------
please provide some sample data and the expected result - this is the fastest way to solution
0
 

Author Comment

by:chokka
ID: 40542432
Thank you for your help.


Attached is the sample data.
C--Users-ZHQ8HPV-Desktop-SampleData.xlsx
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40542492
Please explain how you get your expected results based on the sample data you provided.

I can understand if the expected results was:
200
300
400

I don't see why you expect 500 in the results.
0
 

Author Comment

by:chokka
ID: 40542601
Sorry 500 is an entry mistake. Please apologize
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40542703
For a result of
| SVC_PVR_PYR_NR |
|----------------|
|            200 |
|            300 |
|            400 |

Open in new window

using the provided sample data I simply used a "minus" just as you did in the original question. The only difference is I had to change dat_cx_tm_prd_nr = '85' to '96' as that is needed for the sample data .
select svc_pvr_pyr_nr from tcomgrp
where sls_grp_cd ='IS'
and sales_vew_cd ='S1709'
and dat_cx_tm_prd_nr = '96' --<< NB: this was changed to '96'

minus

select svc_pvr_pyr_nr from tcomgrp
where  sls_grp_cd ='IS'
and sales_vew_cd ='S1709'
and  dat_cx_tm_prd_nr = '0' 
;

Open in new window

           

See this working at sqlfiddle:  http://sqlfiddle.com/#!4/ac02a/1
details:
**Oracle 11g R2 Schema Setup**:

    
    
    CREATE TABLE TCOMGRP 
    	(SVC_PVR_PYR_NR int, DAT_CX_TM_PRD_NR int, SLS_GRP_CD varchar2(2), SALES_VEW_CD varchar2(5))
    ;
    
    INSERT ALL 
    	INTO TCOMGRP ("SVC_PVR_PYR_NR", "DAT_CX_TM_PRD_NR", "SLS_GRP_CD", "SALES_VEW_CD")
    		 VALUES (100, 96, 'IS', 'S1709')
    	INTO TCOMGRP ("SVC_PVR_PYR_NR", "DAT_CX_TM_PRD_NR", "SLS_GRP_CD", "SALES_VEW_CD")
    		 VALUES (100, 96, 'IS', 'S1709')
    	INTO TCOMGRP ("SVC_PVR_PYR_NR", "DAT_CX_TM_PRD_NR", "SLS_GRP_CD", "SALES_VEW_CD")
    		 VALUES (200, 96, 'IS', 'S1709')
    	INTO TCOMGRP ("SVC_PVR_PYR_NR", "DAT_CX_TM_PRD_NR", "SLS_GRP_CD", "SALES_VEW_CD")
    		 VALUES (300, 96, 'IS', 'S1709')
    	INTO TCOMGRP ("SVC_PVR_PYR_NR", "DAT_CX_TM_PRD_NR", "SLS_GRP_CD", "SALES_VEW_CD")
    		 VALUES (300, 96, 'IS', 'S1709')
    	INTO TCOMGRP ("SVC_PVR_PYR_NR", "DAT_CX_TM_PRD_NR", "SLS_GRP_CD", "SALES_VEW_CD")
    		 VALUES (400, 96, 'IS', 'S1709')
    	INTO TCOMGRP ("SVC_PVR_PYR_NR", "DAT_CX_TM_PRD_NR", "SLS_GRP_CD", "SALES_VEW_CD")
    		 VALUES (500, 96, 'IS', 'S1709')
    	INTO TCOMGRP ("SVC_PVR_PYR_NR", "DAT_CX_TM_PRD_NR", "SLS_GRP_CD", "SALES_VEW_CD")
    		 VALUES (500, 96, 'IS', 'S1709')
    	INTO TCOMGRP ("SVC_PVR_PYR_NR", "DAT_CX_TM_PRD_NR", "SLS_GRP_CD", "SALES_VEW_CD")
    		 VALUES (100, 0, 'IS', 'S1709')
    	INTO TCOMGRP ("SVC_PVR_PYR_NR", "DAT_CX_TM_PRD_NR", "SLS_GRP_CD", "SALES_VEW_CD")
    		 VALUES (500, 0, 'IS', 'S1709')
    SELECT * FROM dual
    ;

**Query 1**:

    select svc_pvr_pyr_nr from tcomgrp
    where sls_grp_cd ='IS'
    and sales_vew_cd ='S1709'
    and dat_cx_tm_prd_nr = '96' --<< NB: this was changed to '96'
    
    minus
    
    select svc_pvr_pyr_nr from tcomgrp
    where  sls_grp_cd ='IS'
    and sales_vew_cd ='S1709'
    and  dat_cx_tm_prd_nr = '0' 
    

**[Results][2]**:
    
    | SVC_PVR_PYR_NR |
    |----------------|
    |            200 |
    |            300 |
    |            400 |



  [1]: http://sqlfiddle.com/#!4/ac02a/1

  [2]: http://sqlfiddle.com/#!4/ac02a/1/0

Open in new window

0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40542712
In your original question you state you expect 486 records but only get 101.

The MINUS operator only returns a distinct set of values. If you are wanting all the records not matched between 2 selections then perhaps you need to use NOT EXISTS instead?

From your sample data, this query:
select * --tcomgrp.svc_pvr_pyr_nr
from tcomgrp
where sls_grp_cd ='IS'
and sales_vew_cd ='S1709'
and dat_cx_tm_prd_nr = '96'
and NOT EXISTS (
                select null 
                from tcomgrp exi
                where exi.sls_grp_cd =  tcomgrp.sls_grp_cd
                and exi.sales_vew_cd = tcomgrp.sales_vew_cd
                and exi.svc_pvr_pyr_nr = tcomgrp.svc_pvr_pyr_nr
                and exi.dat_cx_tm_prd_nr = '0' 
               )
;

Open in new window

returns
| SVC_PVR_PYR_NR | DAT_CX_TM_PRD_NR | SLS_GRP_CD | SALES_VEW_CD |
|----------------|------------------|------------|--------------|
|            200 |               96 |         IS |        S1709 |
|            400 |               96 |         IS |        S1709 |
|            300 |               96 |         IS |        S1709 |
|            300 |               96 |         IS |        S1709 |

Open in new window


Compare that to the previous result and there is a difference of
3 rows (from using MINUS) and
4 rows (from using NOT EXISTS)
0
 

Author Comment

by:chokka
ID: 40544550
Thank you all. I didnt know that Minus Query will return only distinct row of records.

My expected output is to get Distinct Row of Records.

If i am able to achieve through minus query, I am happy with that. Probably that 486 row of records are fetched as Duplicate.
0
 

Author Comment

by:chokka
ID: 40544557
Portlet Paul, I am so thankful for you. For taking your time to explain every step of this syntax.
0
 

Author Comment

by:chokka
ID: 40547305
This Post is CLOSED. Please dont comment any further !!

I have a trouble on closing the post because of IE - Javascript Error. I will close it in another Browser.
0
 

Author Closing Comment

by:chokka
ID: 40548169
Thank you !!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses
Course of the Month16 days, 4 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question