?
Solved

Using Minus query in Oracle

Posted on 2014-12-07
4
Medium Priority
?
434 Views
Last Modified: 2014-12-22
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

Open in new window


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
Comment
Question by:chokka
4 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
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
/

Open in new window

0
 
LVL 25

Accepted Solution

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

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
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
      )

Open in new window

0
 

Author Closing Comment

by:chokka
ID: 40514278
Thanks !!
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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 …
Suggested Courses

593 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