locked
Combine two queries into one. RRS feed

  • Question

  • User-34860367 posted

    Hi,

    I am not sure how to combine two results of Oracle queries into one set as following:

    a. 1st query and result

    (SELECT DISTINCT TO_CHAR(InvoiceDate,'YYYY-MM') YEAR_MONTH, SUM(Sales) OVER (PARTITION BY TO_CHAR(InvoiceDate,'YYYYMM')) AS MONTHLY_SALE, 
    SUM(Expenses) OVER (PARTITION BY TO_CHAR(InvoiceDate,'YYYYMM')) AS MONTHLY_EXPENSE FROM view_SaleInformation WHERE TO_CHAR(InvoiceDate,'YYYY-MM') > '2014-12' AND CODE = 'A'

    "YEAR_MONTH" "MONTHLY_SALE" "MONTHLY_EXPENSE"

    "2015-07" 3100 1300
    "2015-06" 5690 1980
    "2015-05" 5300 380
    "2015-04" 14600 3841.6
    "2015-03" 1750 0
    "2015-02" 8600 2529.42
    "2015-01" 2500 0

    b. 2nd query and result

    Query for quarter freight cost as following: 

    SELECT DISTINCT TO_CHAR(ShippingDate,'YYYY-MM') YEAR_MONTH, 
    SUM(FreightCosts) OVER (PARTITION BY TO_CHAR(ShippingDate,'YYYYMM')) AS MONTHLY_FREIGHT FROM view_FreightInformation WHERE TO_CHAR(ShippingDate,'YYYY-MM') > '2014-12' AND CODE = 'A';

    "YEAR_MONTH" "MONTHLY_FREIGHT"
    "2015-06" 700
    "2015-03" 195

    How do I combine two above queries in order to produce the following results: 

    "YEAR_MONTH"| "MONTHLY_SALE" |"MONTHLY_EXPENSE" |"MONTHLY_FREIGHT"
    "2015-07" | 3100 | 1300  | 0
    "2015-06" | 5690 | 1980 | 700
    "2015-05" | 5300 | 380 | 0
    "2015-04" | 14600 | 3841.6 | 0
    "2015-03" | 1750 | 0 | 195
    "2015-02" | 8600 | 2529.42 | 0
    "2015-01" | 2500 | 0

    I tried several outer join and Union syntax but I cannot get the above results. Any possible solution is much appreciated. Thanks in advance.

    Tuesday, August 4, 2015 11:43 AM

Answers

  • User269602965 posted

    try

    SELECT DISTINCT 
    	TO_CHAR(a.InvoiceDate,'YYYY-MM')                                      AS YEAR_MONTH, 
    	a.SUM(a.Sales)    OVER (PARTITION BY TO_CHAR(a.InvoiceDate,'YYYYMM')) AS MONTHLY_SALE,
    	a.SUM(a.Expenses) OVER (PARTITION BY TO_CHAR(a.InvoiceDate,'YYYYMM')) AS MONTHLY_EXPENSE
    	c.MONTHLY_FREIGHT
    FROM 
    	view_SaleInformation a
    INNER JOIN
    	(
    	SELECT DISTINCT 
    		TO_CHAR(b.ShippingDate,'YYYY-MM')                                        AS YEAR_MONTH,        
    		SUM(b.FreightCosts) OVER (PARTITION BY TO_CHAR(b.ShippingDate,'YYYYMM')) AS MONTHLY_FREIGHT
    	FROM 
    		view_FreightInformation b
    	WHERE 
    		TO_CHAR(b.ShippingDate,'YYYY-MM') > '2014-12' 
    		AND 
    		b.CODE = 'A'
    	) c
    	ON TO_CHAR(a.InvoiceDate,'YYYY-MM') = c.YEAR_MONTH  
    WHERE 
    	TO_CHAR(a.InvoiceDate,'YYYY-MM') > '2014-12' 
    	AND 
    	a.CODE = 'A'
    /
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 5, 2015 6:59 PM
  • User-34860367 posted

    Hi,

    After correct a few thing, I got the expected results. The final corrected query as following:

    SELECT DISTINCT 
    	TO_CHAR(a.InvoiceDate,'YYYY-MM')                                      AS YEAR_MONTH, 
    	SUM(a.Sales)    OVER (PARTITION BY TO_CHAR(a.InvoiceDate,'YYYYMM')) AS MONTHLY_SALE,
    	SUM(a.Expenses) OVER (PARTITION BY TO_CHAR(a.InvoiceDate,'YYYYMM')) AS MONTHLY_EXPENSE
    	c.MONTHLY_FREIGHT
    FROM view_SaleInformation a 
    LEFT JOIN
    	(
    	SELECT DISTINCT 
    		TO_CHAR(b.ShippingDate,'YYYY-MM')                                        AS YEAR_MONTH,        
    		SUM(b.FreightCosts) OVER (PARTITION BY TO_CHAR(b.ShippingDate,'YYYYMM')) AS MONTHLY_FREIGHT
    	FROM 
    		view_FreightInformation b
    	WHERE 
    		TO_CHAR(b.ShippingDate,'YYYY-MM') > '2014-12' 
    		AND 
    		b.CODE = 'A'
    	) c
    	ON TO_CHAR(a.InvoiceDate,'YYYY-MM') = c.YEAR_MONTH  
    WHERE TO_CHAR(a.InvoiceDate,'YYYY-MM') > '2014-12' AND a.CODE = 'A';
    

    There are errors in invalid identifiers at 

    a.TO_CHAR(a.InvoiceDate,'YYYY-MM')                                      AS YEAR_MONTH, 
    a.SUM(a.Sales)    OVER (PARTITION BY TO_CHAR(a.InvoiceDate,'YYYYMM')) AS MONTHLY_SALE,
    a.SUM(a.Expenses) OVER (PARTITION BY TO_CHAR(a.InvoiceDate,'YYYYMM')) AS MONTHLY_EXPENSE

    and the results of INNER JOIN was not my expectation. Therefore, I modified above code to fix a few bugs. Anyway, without you I cannot get the solution. Thank you so much.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 6, 2015 10:55 AM

All replies

  • User269602965 posted

    Try

    SELECT
      d.YEAR_MONTH,
      d.MONTHLY_SALE,
      d.MONTHLY_EXPENSE,
      d.MONTHY_FREIGHT
    FROM
      (
      SELECT DISTINCT 
        a.TO_CHAR(a.InvoiceDate,'YYYY-MM')                                    AS YEAR_MONTH, 
        a.SUM(a.Sales)    OVER (PARTITION BY TO_CHAR(a.InvoiceDate,'YYYYMM')) AS MONTHLY_SALE,
        a.SUM(a.Expenses) OVER (PARTITION BY TO_CHAR(a.InvoiceDate,'YYYYMM')) AS MONTHLY_EXPENSE
      FROM 
        view_SaleInformation a
      INNER JOIN
        (
        SELECT DISTINCT 
          TO_CHAR(b.ShippingDate,'YYYY-MM')                                        AS YEAR_MONTH,        
          SUM(b.FreightCosts) OVER (PARTITION BY TO_CHAR(b.ShippingDate,'YYYYMM')) AS MONTHLY_FREIGHT
        FROM 
          view_FreightInformation b
        WHERE 
          TO_CHAR(b.ShippingDate,'YYYY-MM') > '2014-12' 
          AND 
          b.CODE = 'A'
        ) c
        ON TO_CHAR(a.InvoiceDate,'YYYY-MM') = TO_CHAR(c.ShippingDate,'YYYY-MM')
      WHERE 
        TO_CHAR(a.InvoiceDate,'YYYY-MM') > '2014-12' 
        AND 
        a.CODE = 'A'
      ) d
    /
    

    Tuesday, August 4, 2015 12:57 PM
  • User-34860367 posted

    Thank for quickly response. I ran your query and got the following error


    ORA-00904: "C"."ShippingDate": invalid identifier
    00904. 00000 - "%s: invalid identifier"
    *Cause:
    *Action:
    Error at Line: 26 Column: 48

    I tried to change c to b but the same error too. Please take a look and get me a feedback. Thanks again.

    Tuesday, August 4, 2015 2:27 PM
  • User269602965 posted

    Revised

    SELECT
      d.YEAR_MONTH,
      d.MONTHLY_SALE,
      d.MONTHLY_EXPENSE,
      d.MONTHY_FREIGHT
    FROM
      (
      SELECT DISTINCT 
        a.TO_CHAR(a.InvoiceDate,'YYYY-MM')                                    AS YEAR_MONTH, 
        a.SUM(a.Sales)    OVER (PARTITION BY TO_CHAR(a.InvoiceDate,'YYYYMM')) AS MONTHLY_SALE,
        a.SUM(a.Expenses) OVER (PARTITION BY TO_CHAR(a.InvoiceDate,'YYYYMM')) AS MONTHLY_EXPENSE
      FROM 
        view_SaleInformation a
      INNER JOIN
        (
        SELECT DISTINCT 
          TO_CHAR(b.ShippingDate,'YYYY-MM')                                        AS YEAR_MONTH,        
          SUM(b.FreightCosts) OVER (PARTITION BY TO_CHAR(b.ShippingDate,'YYYYMM')) AS MONTHLY_FREIGHT
        FROM 
          view_FreightInformation b
        WHERE 
          TO_CHAR(b.ShippingDate,'YYYY-MM') > '2014-12' 
          AND 
          b.CODE = 'A'
        ) c
        ON TO_CHAR(a.InvoiceDate,'YYYY-MM') = c.YEAR_MONTH  
      WHERE 
        TO_CHAR(a.InvoiceDate,'YYYY-MM') > '2014-12' 
        AND 
        a.CODE = 'A'
      ) d
    /

    Tuesday, August 4, 2015 9:23 PM
  • User-34860367 posted

    There are some same errors for invalid identifiers in your revised query, I can fix easier as following :

        TO_CHAR(a.InvoiceDate,'YYYY-MM')                                    AS YEAR_MONTH, 
        SUM(a.Sales)    OVER (PARTITION BY TO_CHAR(a.InvoiceDate,'YYYYMM')) AS MONTHLY_SALE,
        SUM(a.Expenses) OVER (PARTITION BY TO_CHAR(a.InvoiceDate,'YYYYMM')) AS MONTHLY_EXPENSE

    Please check back your revised query and why it produces the bellowed error: 

    ORA-00904: "D"."MONTHY_FREIGHT": invalid identifier
    00904. 00000 - "%s: invalid identifier"

    Thanks and have a great day!

    Wednesday, August 5, 2015 9:09 AM
  • User269602965 posted

    try

    SELECT DISTINCT 
    	TO_CHAR(a.InvoiceDate,'YYYY-MM')                                      AS YEAR_MONTH, 
    	a.SUM(a.Sales)    OVER (PARTITION BY TO_CHAR(a.InvoiceDate,'YYYYMM')) AS MONTHLY_SALE,
    	a.SUM(a.Expenses) OVER (PARTITION BY TO_CHAR(a.InvoiceDate,'YYYYMM')) AS MONTHLY_EXPENSE
    	c.MONTHLY_FREIGHT
    FROM 
    	view_SaleInformation a
    INNER JOIN
    	(
    	SELECT DISTINCT 
    		TO_CHAR(b.ShippingDate,'YYYY-MM')                                        AS YEAR_MONTH,        
    		SUM(b.FreightCosts) OVER (PARTITION BY TO_CHAR(b.ShippingDate,'YYYYMM')) AS MONTHLY_FREIGHT
    	FROM 
    		view_FreightInformation b
    	WHERE 
    		TO_CHAR(b.ShippingDate,'YYYY-MM') > '2014-12' 
    		AND 
    		b.CODE = 'A'
    	) c
    	ON TO_CHAR(a.InvoiceDate,'YYYY-MM') = c.YEAR_MONTH  
    WHERE 
    	TO_CHAR(a.InvoiceDate,'YYYY-MM') > '2014-12' 
    	AND 
    	a.CODE = 'A'
    /
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 5, 2015 6:59 PM
  • User-34860367 posted

    Hi,

    After correct a few thing, I got the expected results. The final corrected query as following:

    SELECT DISTINCT 
    	TO_CHAR(a.InvoiceDate,'YYYY-MM')                                      AS YEAR_MONTH, 
    	SUM(a.Sales)    OVER (PARTITION BY TO_CHAR(a.InvoiceDate,'YYYYMM')) AS MONTHLY_SALE,
    	SUM(a.Expenses) OVER (PARTITION BY TO_CHAR(a.InvoiceDate,'YYYYMM')) AS MONTHLY_EXPENSE
    	c.MONTHLY_FREIGHT
    FROM view_SaleInformation a 
    LEFT JOIN
    	(
    	SELECT DISTINCT 
    		TO_CHAR(b.ShippingDate,'YYYY-MM')                                        AS YEAR_MONTH,        
    		SUM(b.FreightCosts) OVER (PARTITION BY TO_CHAR(b.ShippingDate,'YYYYMM')) AS MONTHLY_FREIGHT
    	FROM 
    		view_FreightInformation b
    	WHERE 
    		TO_CHAR(b.ShippingDate,'YYYY-MM') > '2014-12' 
    		AND 
    		b.CODE = 'A'
    	) c
    	ON TO_CHAR(a.InvoiceDate,'YYYY-MM') = c.YEAR_MONTH  
    WHERE TO_CHAR(a.InvoiceDate,'YYYY-MM') > '2014-12' AND a.CODE = 'A';
    

    There are errors in invalid identifiers at 

    a.TO_CHAR(a.InvoiceDate,'YYYY-MM')                                      AS YEAR_MONTH, 
    a.SUM(a.Sales)    OVER (PARTITION BY TO_CHAR(a.InvoiceDate,'YYYYMM')) AS MONTHLY_SALE,
    a.SUM(a.Expenses) OVER (PARTITION BY TO_CHAR(a.InvoiceDate,'YYYYMM')) AS MONTHLY_EXPENSE

    and the results of INNER JOIN was not my expectation. Therefore, I modified above code to fix a few bugs. Anyway, without you I cannot get the solution. Thank you so much.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 6, 2015 10:55 AM
  • User269602965 posted

    hard to do when you do not have the data tables

    Thursday, August 6, 2015 12:21 PM
  • User-34860367 posted

    Hi,

    Yes. you're right. Your SQL expertise syntax and logic thinking without visual data are very helpful to me for finding the solution. Please keep up your excellent supports.  Smile

    Thursday, August 6, 2015 3:20 PM