Solved

Using Minus query in Oracle

Posted on 2014-12-07
4
405 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
[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
  • Learn & ask questions
4 Comments
 
LVL 77

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 500 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

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to take different types of Oracle backups using RMAN.
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.

630 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