"paul" <ps******@sympatico.ca> wrote in message
news:b0**************************@posting.google.c om...
thanks for the input
what you have shown me is the inner query I have before the LAG
-the default RANGE when not specified is UNBOUNDED PRECEDING
which in this case gives me ENDING balances that I lag(1,0) to get
opening balances. ie: 19903 open_bal=0, mon_ttl=-172527,
end_bal=-172527
What I want is to makeup data at the end of the range and keep the
ending balance going forward which is a little different than my
original request.
your recognition that i want to dump the lag and show the
ending_balance (and calculate the opening by taking off the current
activity has helped.
What I really want is this
DT BUCKET RUN_TTL
------ ---------- ----------
199903 0192 -172527
199906 0192 374180
199909 0192 95496
199910 0192 1195635
199911 0192 1690914
199912 0192 0
200003 0192 1025431
200006 0192 -706247
200009 0192 1273009
200012 0192 0
200101 0192 0
200102 0192 0
[..]
200301 0192 0
How can 'project' that last row forward?
I am trying in 10G now with partitioned outer joins, but that does not
seem to work if going off of analytic queries, only straight base
tables.
do**************@yahoo.com.au (Douglas Hawthorne) wrote in message
news:<cf**************************@posting.google. com>...
ps******@sympatico.ca (paul) wrote in message
news:<b0**************************@posting.google. com>... I have a query that takes monthly totals and rolls them up to get a
balance at a specific time code
eg:
dt bucket mon_ttl
--- ------ -------
199903 0192 -172527
199906 0192 546707
199909 0192 -278684
199910 0192 1100139
199911 0192 495279
199912 0192 -1690914
200003 0192 1025431
200006 0192 -1731678
200009 0192 1979256
200012 0192 -1273009
Rolls up like
DT BUCKET OPEN
--------- ---------- ----------
199903 0192 0
199906 0192 -172527
199909 0192 374180 (-172527+546707)
199910 0192 95496 etc...
199911 0192 1195635
199912 0192 1690914
200003 0192 0
200006 0192 1025431
200009 0192 -706247
200012 0192 1273009
I get that using
select dt, bucket, lag(run_ttl,1,0)
over (order by dt) open
from
(select dt, bucket, ttl,sum(ttl)
over (partition by bucket order by dt) run_ttl
from
mon_tot
where bucket='0192')
But I want to keep that last balance going forward...
eg: if I want the open as at 200301 and 200012 was the last non-null
balance
I want to carry that forward.
200012 0192 1273009
200101 0192 1273009
[..]
200301 0192 1273009
But I need to 'make up' the data going forward
I'm on 9.2.0.3 anyone have any tips/ideas?
You were almost there except you did not have the RANGE UNBOUNDED
PRECEDING clause. My suggestion is as follows:
SELECT
dt,
bucket,
SUM( ttl )
OVER(
PARTITION BY bucket
ORDER BY dt
RANGE UNBOUNDED PRECEDING
) run_ttl
FROM
mon_tot
WHERE
bucket='0192'
;
My test results are:
DT BUCK RUN_TTL
------ ---- ----------
199903 0192 -172527
199906 0192 374180
199909 0192 95496
199910 0192 1195635
199911 0192 1690914
199912 0192 0
200003 0192 1025431
200006 0192 -706247
200009 0192 1273009
200012 0192 0
10 rows selected.
I found this hint on p.560 of "Expert One-on-One Oracle" by Thomas
Kyte (A-Press:2003). He also has an on-line example at
http://asktom.oracle.com/pls/ask/f?p...D:305416600201
Douglas Hawthorne
Paul,
What you want is a source of rows that have the generated DT and BUCKET
columns with NULL for the TTL column. My suggested solution is to use a
'temporary' table to store the projected values of DT for every BUCKET
value. I then modified my original query to be over an in-line view that is
a UNION ALL of the original table and the projected values table.
First, I created the 'temporary' table to hold the projected values using
CTAS and selecting no rows:
CREATE TABLE projected_mon_tot
AS SELECT * FROM mon_tot
WHERE 1=0
;
Second, I populated this table based on the current values from the MON_TOT
table:
INSERT INTO projected_mon_tot
SELECT
TO_CHAR(
ADD_MONTHS(
TO_DATE(
t.base_dt,
'YYYYMM'
),
a.num_months
),
'YYYYMM'
) AS dt,
t.bucket,
NULL AS ttl
FROM
(
SELECT
rownum AS num_months
FROM
all_objects
WHERE
rownum <= 25
) a,
(
SELECT
MAX( dt ) AS base_dt,
bucket
FROM
mon_tot
GROUP BY
bucket
) t
;
Here I am projecting 25 months past the last DT value for each value in the
BUCKET column by using a cartesian join between a range of row numbers from
the ALL_OBJECTS table and the calculated maximum value of DT for each value
of BUCKET. I have assumed that the DT column is of data type, VARCHAR2 or
CHAR.
Third, I ran the modified query:
SELECT
dt,
bucket,
SUM( ttl )
OVER(
PARTITION BY bucket
ORDER BY dt
) run_ttl
FROM
(
SELECT
*
FROM
mon_tot
UNION ALL
SELECT
*
FROM
projected_mon_tot
)
WHERE
bucket='0192'
;
The only difference between this query and my original one is the use of the
in-line view with the UNION ALL. I used UNION ALL because I know that there
are no overlaps in values between the two tables.
Thanks for pointing out that RANGE UNBOUNDED PRECEEDING is the default.
The result is:
DT BUCK RUN_TTL
------ ---- ----------
199903 0192 -172527
199906 0192 374180
199909 0192 95496
199910 0192 1195635
199911 0192 1690914
199912 0192 0
200003 0192 1025431
200006 0192 -706247
200009 0192 1273009
200012 0192 0
200101 0192 0
200102 0192 0
200103 0192 0
200104 0192 0
200105 0192 0
200106 0192 0
200107 0192 0
200108 0192 0
200109 0192 0
200110 0192 0
200111 0192 0
200112 0192 0
200201 0192 0
200202 0192 0
200203 0192 0
200204 0192 0
200205 0192 0
200206 0192 0
200207 0192 0
200208 0192 0
200209 0192 0
200210 0192 0
200211 0192 0
200212 0192 0
200301 0192 0
35 rows selected.
Douglas Hawthorne
