Solved

Oracle - Opposite to INTERSECT Keyword - Query Syntax clarification.

Posted on 2015-01-14
12
283 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 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
 

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 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

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!

Question has a verified solution.

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

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
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.

734 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