Reference select sub-query field

Question

I have the following code that uses a sub-query in the select statement to find sum of sales for a particular year. what i would like to do is reference that sum again in the select statement in order to determine the % increase or decrease from year to year. but im currently receiving an "invalid identifier" error when I attempted to do so.

SELECT    mc.rsm as terr
    , mc.salesman_code
        , mc.customer_id
        , mc.name
        , mc.city
        , mc.state
        , mc.discount
        , DECODE(cp.customer_id,NULL,'N','Y') as price_list
        , DECODE(ca.customer_no,NULL,'N','Y') as agreement
        , i.FY_sales
        ,(SELECT SUM(inv.sales) as Last_FY_sales                
            FROM ifsinfo.hb_invoicing_all inv 
            WHERE inv.site IN ('06','01')
                AND TO_DATE(inv.invdate) between Trunc(sysdate,'Year')-458 and Trunc(sysdate,'Year')-92
                And inv.customer_id = mc.customer_id            
               HAVING SUM(inv.sales) <> 0
           )  FY_Last_Year 
        , i.FY_sales / FY_Last_Year as Percent_increaseORdecrease         
FROM iffo.info mc 
LEFT JOIN(SELECT inv.company as company
               , inv.customer_id as cust
               , inv.address_id
               , SUM(inv.sales) as FY_sales
               , SUM(inv.cost) as costs 
            FROM ifsinfo.hb_invoicing_all inv 
            WHERE inv.site IN ('06','01')           
                AND TO_DATE(inv.invdate) between Trunc(sysdate,'Year')-92 and add_months(trunc(sysdate,'YEAR'),12)-93                 
            GROUP BY inv.company
               , inv.customer_id
               , inv.address_id `enter code here`
            HAVING SUM(inv.sales) <> 0
           ) i                    
ON (mc.company = i.company)
    AND (mc.customer_id = i.cust)
    AND (mc.address_id = i.address_id)

Answer 1

Here is a solution which merges the two queries on ifsinfo.hb_invoicing_all into one, using CASE() statements to maintain the aggregates for different years. This means you avoid duplicating the code whilst still having all values within scope of the main query.

SELECT  mc.rsm as terr
        , mc.salesman_code
        , mc.customer_id
        , mc.name
        , mc.city
        , mc.state
        , mc.discount
        , DECODE(cp.customer_id,NULL,'N','Y') as price_list
        , DECODE(ca.customer_no,NULL,'N','Y') as agreement
        , i.FY_sales
        , i.FY_Last_Year 
        , i.FY_sales / i.FY_Last_Year as Percent_increaseORdecrease         
FROM iffo.info mc 
LEFT JOIN  (SELECT inv.company as company
               , inv.customer_id as cust
               , inv.address_id
               , SUM(case when TO_DATE(inv.invdate) between Trunc(sysdate,'Year')-92 and add_months(trunc(sysdate,'YEAR'),12)-93 
                          then   inv.sales else 0 end) as FY_sales
               , SUM(case when TO_DATE(inv.invdate) between Trunc(sysdate,'Year')-92 and add_months(trunc(sysdate,'YEAR'),12)-93 
                          then   inv.cost else 0 end) as ) as costs 
              , SUM(case when TO_DATE(inv.invdate) between Trunc(sysdate,'Year')-458 and Trunc(sysdate,'Year')-92
                         then   inv.sales else 0 end) as FY_Last_Year 
            FROM ifsinfo.hb_invoicing_all inv 
            WHERE inv.site IN ('06','01')           
                AND TO_DATE(inv.invdate) between  Trunc(sysdate,'Year')-458 and add_months(trunc(sysdate,'YEAR'),12)-93                 
            GROUP BY inv.company
               , inv.customer_id
               , inv.address_id
            HAVING SUM(inv.sales) <> 0
           )       i  
ON (mc.company = i.company)
    AND (mc.customer_id = i.cust)
    AND (mc.address_id = i.address_id);

caveat: the question contains no schema definition or sample data, so syntax correctness is not guaranteed.

Answer 2

You could try extracting the sub-query into a CTE:

with sales(customer_id, Last_FY_sales) as (
  SELECT inv.customer_id, SUM(inv.sales) as Last_FY_sales                
  FROM ifsinfo.hb_invoicing_all inv 
  WHERE inv.site IN ('06','01')
  AND TO_DATE(inv.invdate) between Trunc(sysdate,'Year')-458 and Trunc(sysdate,'Year')-92
  GROUP BY inv.customer_id           
  HAVING SUM(inv.sales) <> 0
)

SELECT    mc.rsm as terr
        , mc.salesman_code
        , mc.customer_id
        , mc.name
        , mc.city
        , mc.state
        , mc.discount
        , DECODE(cp.customer_id,NULL,'N','Y') as price_list
        , DECODE(ca.customer_no,NULL,'N','Y') as agreement
        , i.FY_sales
        , sales.Last_FY_sales FY_Last_Year 
        , i.FY_sales / sales.Last_FY_sales as Percent_increaseORdecrease         
FROM iffo.info mc 
LEFT JOIN sales on sales.customer_id = mc.customer_id
LEFT JOIN(SELECT inv.company as company
               , inv.customer_id as cust
               , inv.address_id
               , SUM(inv.sales) as FY_sales
               , SUM(inv.cost) as costs 
          FROM ifsinfo.hb_invoicing_all inv 
          WHERE inv.site IN ('06','01')           
                AND TO_DATE(inv.invdate) between Trunc(sysdate,'Year')-92 and add_months(trunc(sysdate,'YEAR'),12)-93                 
          GROUP BY inv.company
               , inv.customer_id
               , inv.address_id `enter code here`
          HAVING SUM(inv.sales) <> 0
           ) i                    
ON (mc.company = i.company)
    AND (mc.customer_id = i.cust)
    AND (mc.address_id = i.address_id)

Answer 3

Add your sub query as a join:

SELECT ...., 
  inv.Last_FY_sales, i.FY_sales / inv.FY_Last_Year as Percent_increaseORdecrease 
FROM ...
JOIN (SELECT customer_id, SUM(inv.sales) as Last_FY_sales                
      FROM ifsinfo.hb_invoicing_all inv 
      WHERE inv.site IN ('06','01')
      AND TO_DATE(inv.invdate) between Trunc(sysdate,'Year')-458 and 
                Trunc(sysdate,'Year')-92
      GROUP BY inv.customer_id             
      HAVING SUM(inv.sales) <> 0 ) inv 
         ON inv.customer_id = mc.customer_id