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

x
Solved

# Using Minus query in Oracle

Posted on 2014-12-07
Medium Priority
420 Views
I have two tables

CCYEXC
CCYCD        --  Currency Code
CCYRTE       -- Currency Rate
YEARNR       -- Currency Year

CCYDCD
CCYCD          -- Currency Code
CCYCDDSC  -- Currency Description

Table : CCYEXC has 182 records for the year = 2013 and 81 records for the year = 2014

On running the minus query against the currency code : CCYCD , query returns 101 records.

``````WITH CTE
AS
(
SELECT a.CCYCD
FROM CCYEXC  a
WHERE  a.YEARNR = 2013
MINUS
SELECT a.CCY_CD
FROM CCYEXC  a
WHERE  a.YEARNR = 2014
)
SELECT CTE.CCYCD,DCD.CCYCDDSC
FROM CTE,CCYDCD DCD
WHERE CTE.CCYCD = DCD.CCYCD
``````

I have to get the end results

101 records of 2013 - Currency Code, Currency Description, Currency Rate ( 2013 ).

I am not able to get the currency rate : CCYEXC
0
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

LVL 77

Expert Comment

ID: 40486108
Please provide sample data and expected results.

Is 2013 the defining value or do you just want the minimum rate available?

Based on what you posted, I'm thinking something like:
``````select xc.ccycd, ccytre, yearnr
from
(
select ccycd, ccyrte, yearnr
from
(
select ccycd, ccyrte, yearnr, row_number() over(order by ccycd, yearnr) rn
from ccyexy
)
where rn=1
) xc join ccydcd cd on xc.ccycd=cs.ccycd
/
``````
0

LVL 25

Accepted Solution

chaau earned 2000 total points
ID: 40486109
You need to join CCYEXC again:
``````WITH CTE
AS
(
SELECT a.CCYCD
FROM CCYEXC  a
WHERE  a.YEARNR = 2013
MINUS
SELECT a.CCYCD
FROM CCYEXC  a
WHERE  a.YEARNR = 2014
)
SELECT CTE.CCYCD,DCD.CCYCDDSC, E.CCYEXC
FROM CTE INNER JOIN CCYDCD DCD
ON CTE.CCYCD = DCD.CCYCD
INNER JOIN CCYEXC  E ON CTE.CCYCD = E.CCYCD AND E.YEARNR = 2013
``````
0

LVL 49

Expert Comment

ID: 40486138
Perhaps using NOT EXISTS would suffice:
``````SELECT
CCYEXC.CCYCD
, CCYEXC.CCYRTE
, CCYEXC.YEARNR
, CCYDCD.CCYCDDSC
FROM CCYEXC
INNER JOIN CCYDCD ON CCYEXC.CCYCD = CCYDCD.CCYCD
WHERE CCYEXC.YEARNR = 2013
AND NOT EXISTS (
SELECT
NULL
FROM CCYEXC A
WHERE A.YEARNR = 2014
AND CCYEXC.CCYCD = A.CCYCD
)
``````
0

Author Closing Comment

ID: 40514278
Thanks !!
0

## Featured Post

Question has a verified solution.

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

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
###### Suggested Courses
Course of the Month6 days, 16 hours left to enroll