Re: What changed from the previous row

On 12/10/2024 9:50 AM, Greg Wilburn wrote:

Our ERP has a bunch of Audit tables... a record/row is created each time something changes in (for example) the Part Master file.
When a change is made, an audit table row is created containing the values of all of the columns as the production table plus the transaction date, transaction time, and user.
To know what "changed", you have to compare it to the previously created audit row.

I'm trying to figure out how to recognize the column(s) that changed versus the previous row of data, using SQL.
I know how to do this in an RPG program, but have yet to come up with an SQL equivalent.

Any ideas?

Perhaps OLAP LAG?

with details as (
select
table_schema, table_name, ordinal_position, column_name, data_type, Length, numeric_scale, column_heading,
lag(column_name)
over
(partition by table_schema, table_name
order by table_schema, table_name, column_name)
as prev_column_name,
lag(data_type)
over
(partition by table_schema, table_name
order by table_schema, table_name, column_name)
as prev_data_type
from syscolumns
where table_schema = 'QGPL' and table_name = 'QATTINFO'
order by table_schema, table_name, column_name)

select *
from details
where data_type <> prev_data_type
or prev_data_type is null
order by table_schema, table_name, column_name
;

https://www.ibm.com/docs/en/i/7.4?topic=expressions-olap-specifications