DISTINCT in Oracle PL/SQL SELECT

Question

User-34860367 posted

Hello all,

ViewInvoice data result:

YearMonth | MonthlySale | MonthlyFee

2015-04 | 3000 | 250

2015-03 | 400 | 170

2015-02 | 5000 | 500

2015-01 | 600 | 250

----------------------------------------------------------------------------------------

ViewShipping data result:

YearMonth | MonthlyShipping

2015-04 | 50

2015-03 | 60

2015-01 | 20

I like to combine two above view results into the following expected data 

YearMonth | MonthlySale | MonthlyFee | MonthlyShipping

2015-04 | 3000 | 250 | 50

2015-03 | 400 | 170 | 60

2015-02 | 5000 | 500 | 0          

2015-01 | 600 | 250 | 20

-----------------------------------------------------------------------------------

I try to run the below query: 

SELECT DISTINCT vw1.YearMonth, vw1.MonthlySale, vw1.MonthlyFee,  (CASE WHEN vw1.YearMonth = vw2.YearMonth THEN vw2.MonthlyShipping ELSE 0 END) AS MonthlyShipping

FROM ViewInvoice vw1, ViewShipping vw2

ORDER BY vw1.YearMonth DESC;

and my query result

YearMonth | MonthlySale | MonthlyFee | MonthlyShipping

2015-04    |  3000 |  250 | 50

2015-04    |  3000 |  250 | 0

2015-03    |  400 |  170 | 60

2015-03    |  400 |  170 | 60

2015-02 | 5000 | 500 | 0        

2015-01 | 600 | 250 | 20

2015-01 | 600 | 250 | 0

===========================================================================================================

Is it distinct not working in above result for duplicated YearMonth? Any correction in my query to produce my expected result? I appreciate your time to help me the solution. Thanks in advance. 

Answer 1

User269602965 posted

You are using a Cartesian Join which returns all possible combinations and permutations of variables.

Try left outer join

SELECT 
  vw1.YearMonth, 
  vw1.MonthlySale, 
  vw1.MonthlyFee,
  NVL(vw2.MonthlyShipping,0) as  MonthlyShipping
FROM 
  ViewInvoice vw1
LEFT OUTER JOIN ViewShipping vw2 on vw1.YearMonth = vw2.YearMonth
ORDER BY 
  vw1.YearMonth DESC;

Answer 2

User-34860367 posted

Thanks again, your query worked perfectly. I got one more issue how to handle null on this expression (MonthlySale - (MonthlyFee + MonthlyShipping) ) as MonthlyNet? Any suggestion how to overcome? 

Answer 3

User-34860367 posted

I found the solution how to check on NULL value as following:

(CASE WHEN Monthly_Freight IS NOT NULL THEN (MonthlySale - (MonthlyFee + MonthlyShipping))
          WHEN MonthlyShipping IS NULL THEN (MonthlySale - MonthlyFee)
END) as MonthlyNet.