Solved

Oracle - Opposite to INTERSECT Keyword - Query Syntax clarification.

Posted on 2015-01-14
12
224 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 31

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

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…
APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

708 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

15 Experts available now in Live!

Get 1:1 Help Now