Using Minus query in Oracle

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
chokkaStudentAsked:
Who is Participating?
 
chaauCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
PortletPaulfreelancerCommented:
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
 
chokkaStudentAuthor Commented:
Thanks !!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.