RE: What changed from the previous row

Thanks... not too familiar with that.

Wondering how well that will work when dealing with over 100 columns of data that could potentially change?

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Buck Calabro
Sent: Tuesday, December 10, 2024 1:03 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: 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


--
--buck

http://wiki.midrange.com
Your updates make it better!


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Greg Wilburn
Director of IT
301.895.3792 ext. 1231