You Asked
Hi Tom,
I have the following view definition:
create or replace view jl_test_summary_v as
select to_char(b.invoice_date, 'YYYY') year, a.sku sku,
a.part_number part_number, a.model,
sum(decode(to_char(b.invoice_date, 'MM'), '01', quantity, 0)) JAN_QTY,
sum(decode(to_char(b.invoice_date, 'MM'), '02', quantity, 0)) FEB_QTY,
sum(decode(to_char(b.invoice_date, 'MM'), '03', quantity, 0)) MAR_QTY,
sum(decode(to_char(b.invoice_date, 'MM'), '04', quantity, 0)) APR_QTY,
sum(decode(to_char(b.invoice_date, 'MM'), '05', quantity, 0)) MAY_QTY,
sum(decode(to_char(b.invoice_date, 'MM'), '06', quantity, 0)) JUN_QTY,
sum(decode(to_char(b.invoice_date, 'MM'), '07', quantity, 0)) JUL_QTY,
sum(decode(to_char(b.invoice_date, 'MM'), '08', quantity, 0)) AUG_QTY,
sum(decode(to_char(b.invoice_date, 'MM'), '09', quantity, 0)) SEP_QTY,
sum(decode(to_char(b.invoice_date, 'MM'), '10', quantity, 0)) OCT_QTY,
sum(decode(to_char(b.invoice_date, 'MM'), '11', quantity, 0)) NOV_QTY,
sum(decode(to_char(b.invoice_date, 'MM'), '12', quantity, 0)) DEC_QTY,
nvl(sum(b.quantity),0) TOT_SALES
from jl_inventory_all_v a, jl_line_items b
where a.sku = b.sku
and b.category = 'Subwoofers'
and b.true_warranty = 'N'
and substr(b.sku,1,1) = '9'
group by to_char(b.invoice_date, 'YYYY'), a.sku, a.part_number, a.model
/
I need to include the total sku quantity for the previous year as well as the previous 2 years (in 2 separate columns) in this view also. How do I go about doing this? The database version is 8.1.7.0.1.
and Tom said...
With 816 EE and up, we have very very cool analytic functions. In your case, the answer is the LAG function:
ops$tkyte@ORA817DEV.US.ORACLE.COM> select year, sku, part_number, model, tot_sales,
2 lag(tot_sales) over ( order by year ) prior_year,
3 lag(tot_sales,2) over ( order by year ) two_years_ago
4 from jl_test_summary_v;
YEAR SKU PART_NUMBER MODEL TOT_SALES PRIOR_YEAR TWO_YEARS_AGO
---- ---------- ----------- ---------- ---------- ---------- -------------
1999 9876 1 1 7800
2000 9876 1 1 15600 7800
2001 9876 1 1 7800 15600 7800
The lag function lets us reach back to prior (or subsequent with LEAD) rows in the result set and incorporate their results in the current row.
If you have my book, I have a whole chapter on these functions. You can also refer to the SQL reference manual (list of functions and syntax) AND the data warehousing guide (exapmles, explanation) for details.
(i created dummy data for this example via)
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into a values ( 9876, 1, 1 );
1 row created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> begin
2 for i in 1 .. 12
3 loop
4 insert into b values ( 9876, add_months(to_date('01-jan-2001'), i-1), 100*i, 'Subwoofers', 'N' );
5 end loop;
6 insert into b
7 select sku, add_months( invoice_date, -12 ), quantity, category, true_warranty from b;
8 insert into b
9 select sku, add_months( invoice_date, -12 ), quantity, category, true_warranty from b;
10 end;
11 /
Rating
(6 ratings)
Is this answer out of date? If it is, please let us know via a Comment