Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle - Opposite to INTERSECT Keyword - Query Syntax clarification.

Posted on 2015-01-14
12
Medium Priority
?
339 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 2
  • 2
  • +1
12 Comments
 
LVL 77

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 77

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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 32

Expert Comment

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

Assisted Solution

by:sdstuber
sdstuber earned 2000 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
 

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 74

Accepted Solution

by:
sdstuber earned 2000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

670 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