Solved

Using Minus query in Oracle

Posted on 2014-12-07
4
366 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 76

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 24

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 48

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 26 59
Add different cell to otherwise similiar row 4 37
create a nested synonym 4 23
Create a MySQL table as easily as possible 6 34
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
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…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

815 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

13 Experts available now in Live!

Get 1:1 Help Now