Hi ,

how to compare the yesterdays values and the todays in a table based on the inserted date. the output should show the difference alone. i have a separate column for updated date but i should not use (current date - 1 days) to get the yesterday's values

Pls find the description of that table.

Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
NAME SYSIBM VARCHAR 32 0 Yes
VALUE SYSIBM VARCHAR 256 0 Yes
VALUE_FLAGS SYSIBM VARCHAR 10 0 Yes
DEFERRED_VALUE SYSIBM VARCHAR 256 0 Yes
DEFERRED_VALUE_FLAGS SYSIBM VARCHAR 10 0 Yes
DATATYPE SYSIBM VARCHAR 128 0 Yes
DBPARTITIONNUM SYSIBM SMALLINT 2 0 Yes
UPDATED_DATE SYSIBM DATE 4 0 Yes

any help is appreciated

Thanks
TJ

Please publish sample/test data and expected output from the sample/test data
with one of the following format
(1) CREATE TABLE statement and INSERT statement.
or
(2) WITH common-table-expression.

NAME VALUE UPDATED_DATE
---- ----- ---------------

avg_appl 5 1/06/2012

app_ctl_heap_sz 16384 1/06/2012

avg_appl 3 1/07/2012

app_ctl_heap_sz 16384 1/07/2012



we have to compare the rows based on the updated_date (between yesterday and today) and return the yesterday's value incase of any change.


avg_appl 5 1/06/2012

note : ther are nearly 800 rows updated daily it has to compare all the 800 rows of yesterday and today


by TJ

A solution may be to use LEAD OLAP specification.

any other way ?

Why?
Was there any issue on LEAD OLAP specification?

Please see Information Center about OLAP specifications including LEAD
OLAP specifications - IBM DB2 9.7 for Linux, UNIX, and Windows

Could you make a required query?

If you published sample/test data with the format I hoped, I could show you an example.