Solved

Using Minus query in Oracle

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
best datatype for oracle table email creation 8 57
Optimize the query 5 43
Postgresql: Import csv constant errors, how to fix? 9 66
SQL Syntax Grouping Sum question 7 27
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

828 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