Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle - Opposite to INTERSECT Keyword - Query Syntax clarification.

Posted on 2015-01-14
12
Medium Priority
?
359 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 78

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 78

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

972 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