Solved

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

Posted on 2015-01-09
14
291 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 48

Expert Comment

by:PortletPaul
Comment Utility
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 20

Expert Comment

by:flow01
Comment Utility
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
Comment Utility
@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
 

Author Comment

by:chokka
Comment Utility
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 48

Expert Comment

by:PortletPaul
Comment Utility
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
Comment Utility
Thank you for your help.


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

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:chokka
Comment Utility
Sorry 500 is an entry mistake. Please apologize
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Portlet Paul, I am so thankful for you. For taking your time to explain every step of this syntax.
0
 

Author Comment

by:chokka
Comment Utility
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
Comment Utility
Thank you !!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
how to tune this sql query 61 100
Adding a blank row when using union all 4 49
oracle query 15 63
Oracle regular expression 6 29
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

744 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now