Reputation: 23
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)
Upvotes: 0
Views: 58
Reputation: 146349
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.
Upvotes: 0
Reputation: 5290
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)
Upvotes: 1
Reputation: 1542
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
Upvotes: 0