Hello all, this is a puzzle thats been bugging me for sometime, and
now it looks like I *have* to build something.

I would like to know when a code number changed for an account in a
delta table.
The table contains the account number, several code numbers, a row
insert date and basically looks like this
DPLH_ROW_EFFCT_DTE DPLH_PLAN_NUM USERID_NAM DPLH_SIOB_CDE
DPLH_SLIQ_CDE
4/15/2009 164328 LACSOA1 99 99
4/30/2009 164328 LACSOA1 5 99
5/19/2009 164328 LACSOA1 101 105

There are many other fields, so records could be added unrelated to
the code numbers I'm interested in. So my challenged is to identify
any DPLH_PLAN_NUMs that have had their DPLH_SIOB_CDE or SLIQ_CDE
changed. It seems I need to compare the last record to the second last
record. Is this even possible?

It seems I need to compare the last record to the second last
Check out analytic functions, especially LAG() and LEAD(). At
asktom.oracle.com you will find several threads about this, including
clear examples from Tom.

On May 21, 8:17*am, Alt255 <alt255.2... (AT) gmail (DOT) com> wrote:
This might give you a start:

SQL> create table dplh(
2 dplh_row_effct_dte date,
3 dplh_plan_num number,
4 userid_name varchar2(10),
5 dplh_siob_cde number,
6 dplh_sliq_cde number
7 );

Table created.

SQL>
SQL> insert all
2 into dplh
3 values(to_date('04/15/2009','MM/DD/RRRR'), 164328, 'LACSOA1', 99,
99)
4 into dplh
5 values(to_date('04/30/2009','MM/DD/RRRR'), 164328, 'LACSOA1', 5,
99)
6 into dplh
7 values(to_date('05/19/2009','MM/DD/RRRR'), 164328, 'LACSOA1',
101, 105)
8 select * From dual;

3 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select dplh_plan_num,
2 dplh_siob_cde,
3 lag(dplh_siob_cde) over (order by dplh_row_effct_dte,
dplh_plan_num) prior_siob_cde,
4 dplh_sliq_cde,
5 lag(dplh_sliq_cde) over (order by dplh_row_effct_dte,
dplh_plan_num) prior_sliq_cde
6 from dplh;

DPLH_PLAN_NUM DPLH_SIOB_CDE PRIOR_SIOB_CDE DPLH_SLIQ_CDE
PRIOR_SLIQ_CDE
------------- ------------- -------------- -------------
--------------
164328 99 99
164328 5 99 99
99
164328 101 5 105
99

SQL>


David Fitzjarrell

On May 21, 10:10*am, ddf <orat... (AT) msn (DOT) com> wrote:
Another possibility:

SQL> with cde_comp as (
2 select dplh_plan_num,
3 dplh_siob_cde,
4 lag(dplh_siob_cde) over (order by dplh_row_effct_dte,
dplh_plan_num) prior_siob_cde,
5 dplh_sliq_cde,
6 lag(dplh_sliq_cde) over (order by dplh_row_effct_dte,
dplh_plan_num) prior_sliq_cde
7 from dplh)
8 select dplh_plan_num, dplh_siob_cde,
9 case when prior_siob_cde <> dplh_siob_cde then
prior_siob_cde else null end prior_siob_cde,
10 dplh_sliq_cde,
11 case when prior_sliq_cde <> dplh_sliq_cde then
prior_sliq_cde else null end prior_sliq_cde
12 from cde_comp;

DPLH_PLAN_NUM DPLH_SIOB_CDE PRIOR_SIOB_CDE DPLH_SLIQ_CDE
PRIOR_SLIQ_CDE
------------- ------------- -------------- -------------
--------------
164328 99 99
164328 5 99 99
164328 101 5 105
99

SQL>

Reports only changed values between records (notice the SIOB value
changed in the second record and the SLIQ value did not).


David Fitzjarrell

On May 21, 10:17*am, ddf <orat... (AT) msn (DOT) com> wrote:
I ran the SQL and something is still not right Here is one example
from your SQL.

DPLH_PLAN_NUM DPLH_SIOB_CDE PRIOR_SIOB_CDE
210935 103 101

I did some changes to the SQL to test only 1 field
with cde_comp as (
select dplh_plan_num, dplh_siob_cde, lag(dplh_siob_cde) over (order
by dplh_row_effct_dte, dplh_plan_num) prior_siob_cde
from wh_dplh_all where dplh_row_effct_dte = '20-MAY-2009' )
select dplh_plan_num, dplh_siob_cde,
case when prior_siob_cde <> dplh_siob_cde then
prior_siob_cde else null end prior_siob_cde
from cde_comp



I added that the date of the current record must be May 20
(Yesterday)

DPLH_NUM DPLH_ROW_EFFCT_DTE DPLH_PLAN_NUM DPLH_SIOB_CDE
812389 7/28/2005
210935 2
3271626 9/17/2006
210935 106
4960272 12/17/2006
210935 103
6676619 11/23/2007
210935 103
7794027 5/20/2009
210935 103
As you can see SIOB did not change, and also it never ever had a 101
value (though its a valid code). I Compared all the fields from the
May 20 to the November 23 and there was no difference. This means
someone was reviewing the plan, made no changes, and closed the query.



I also checked this one

DPLH_PLAN_NUM DPLH_SIOB_CDE PRIOR_SIOB_CDE
219191 101 103


An the detail history:

DPLH_NUM DPLH_ROW_EFFCT_DTE DPLH_PLAN_NUM DPLH_SIOB_CDE
878331 7/28/2005
219191 4
3336823 9/17/2006
219191 106
4518721 11/2/2006
219191 101
6549221 10/24/2007
219191 101
7794047 5/20/2009
219191 101

This the prior_siob is miss reporting the code number. Your code seems
to make sense I checked the previous record in the table to make sure
it wasn't picking up that record, and that doesn't match

On May 21, 1:38*pm, Alt255 <alt255.2... (AT) gmail (DOT) com> wrote:
My intent was to provide you a starting point, and apparently I have
been successful in that attempt. Not having the exact table and data
you have and not knowing what output you're wanting makes it very
difficult to produce code which provides the desired outcome. It's
your turn to play with the examples and modify them to meet your
needs.

Of course if you choose to provide the ddl for the table, some sample
data and a description of what, exactly, you want to see in this
report a more specific example/solution can be offered.


David Fitzjarrell

On May 22, 8:17*am, ddf <orat... (AT) msn (DOT) com> wrote:
Yes thanks David, I realised this after I posted, I'm in contact with
my DB group to look at the speficis of the table. Thanks for the help