locked
DISTINCT in Oracle PL/SQL SELECT RRS feed

  • 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. 

    Tuesday, April 28, 2015 11:37 AM

Answers

  • 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;
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 28, 2015 9:35 PM
  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 29, 2015 11:57 AM

All replies

  • 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;
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 28, 2015 9:35 PM
  • 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? 

    Wednesday, April 29, 2015 11:09 AM
  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 29, 2015 11:57 AM