Solved

Oracle - Opposite to INTERSECT Keyword - Query Syntax clarification.

Posted on 2015-01-14
12
237 Views
Last Modified: 2015-01-21
In Oracle 11g.

Value for cus_sls_csf_cd is not matching in these two queries.

I need to get all cus_sys_nr values for mismatching cus_sls_csf_cd.  I need an opposite of INTERSECT Syntax.

How should i generate the mismatch row of records.

select cus_sls_csf_cd from TCUSPHS
where cus_sys_nr = 3308
and dat_cx_tm_prd_nr = 0;


select cus_sls_csf_cd from TCUSPHS
where cus_sys_nr = 3308
and dat_cx_tm_prd_nr = 96;

Open in new window

0
Comment
Question by:chokka
  • 7
  • 2
  • 2
  • +1
12 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40549689
Typically the minus from your previous question.

What isn't working?  Sample data and expected results would help.
0
 

Author Comment

by:chokka
ID: 40549720
I tried this query ..

with cte as
(
        select A.cus_sys_nr from TCUSPHS A
        where A.dat_cx_tm_prd_nr = 0
        and A.cus_sls_csf_cd not in 
        (
        select B.cus_sls_csf_cd from TCUSPHS  B
        where B.dat_cx_tm_prd_nr = 96
        and A.cus_sys_nr = B.cus_sys_nr
        )
)
select count(*) from cte

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40549747
>>I tried this query ..

I don't know what that is trying to tell me.

To get a list of items in one result that isn't in another result, you normally use the MINUS query from your other question.

I'm assuming that isn't working for some reason.

I would like to see sample data of what isn't 'working'.
0
 
LVL 32

Expert Comment

by:awking00
ID: 40549766
Do a full outer join then minus the intersect.
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 40549806
no union, join, intersect, minus or other double query needed.
Simply select both in one pass and filter out any that only have 1 set of dat_cx_tm_prd_nr values

select cus_sls_csf_cd from TCUSPHS
where cus_sys_nr = 3308
and dat_cx_tm_prd_nr in (0,96)
group by cus_sls_csf_cd
having count(distinct dat_cx_tm_prd_nr) = 1
0
 

Author Comment

by:chokka
ID: 40558412
This post is closed. Please dont comment any further.

I will close this post later today.
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: 40558722
I am sorry post is not closed. Still i have the problem on creating the query.
0
 

Author Comment

by:chokka
ID: 40558741
This query is based on UNION. I actually want  the mismatching cus_sys_nr between two contexts if the values for cus_sls_csf_cd is not same.

select sls_pln_ver_typ_cd,cus_sys_nr,cus_sls_csf_cd,dat_cx_tm_prd_nr from tcusphs
where 
cus_sys_nr = '3308' and
dat_cx_tm_prd_nr = 96

union


select sls_pln_ver_typ_cd,cus_sys_nr,cus_sls_csf_cd,dat_cx_tm_prd_nr from tcusphs
where 
cus_sys_nr = '3308' and
dat_cx_tm_prd_nr = 0

Open in new window

C--Users-ZHQ8HPV-Desktop-Defect.xls
0
 

Author Comment

by:chokka
ID: 40558746
Technically, i have to write a self join query for the same with two alias name A and B
0
 

Author Comment

by:chokka
ID: 40558779
And this is my syntax, I am getting incorrect output. I am getting the ouput of matching - cus_sls_csf_cd

select A.cus_sys_nr
from tcusphs A, tcusphs B
where A.cus_sys_nr = B.cus_sys_nr
and A.cus_sls_csf_cd <> B.cus_sls_csf_cd
and A.dat_cx_tm_prd_nr in ( 0, 96)

Open in new window

0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40559707
Did you try any of the posts above?  If they don't work,  please post sample data with expected results.

Extend this example.
The WITH clause is only to provide sample data, since we don't have your table; it's not part of the actual query you would run.

WITH tcusphs
     AS (SELECT 'Y' cus_sls_csf_cd, 0 dat_cx_tm_prd_nr, 3308 cus_sys_nr FROM DUAL
         UNION ALL
         SELECT 'X', 96, 3308 FROM DUAL
         UNION ALL
         SELECT 'X', 0, 3308 FROM DUAL
         UNION ALL
         SELECT 'X', 96, 3308 FROM DUAL
         UNION ALL
         SELECT 'Z', 0, 3308 FROM DUAL)
  SELECT cus_sls_csf_cd
    FROM tcusphs
   WHERE cus_sys_nr = 3308 AND dat_cx_tm_prd_nr IN (0, 96)
GROUP BY cus_sls_csf_cd
  HAVING COUNT(DISTINCT dat_cx_tm_prd_nr) = 1


and, if this small example does not provide the results you would expect, then show what the results should be using that data (or, extend the data and show the expected results)
0
 

Author Closing Comment

by:chokka
ID: 40563573
Thanks
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
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 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.

920 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

12 Experts available now in Live!

Get 1:1 Help Now