Solved

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

Posted on 2015-01-09
14
302 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
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 20

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
 

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 48

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 76

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
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
ID: 40542601
Sorry 500 is an entry mistake. Please apologize
0
 
LVL 48

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 48

Accepted Solution

by:
PortletPaul earned 500 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

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.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

896 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

14 Experts available now in Live!

Get 1:1 Help Now