Question Re: HANA SQL loop over columns in Technology Q&A

HANA SQL loop over columns

martin_chambers — Sat, 25 Aug 2018 15:08:44 GMT

I'm working in SAP BW powered by HANA. You can transform data using SQL in AMDPs (ABAP Managed Database Procedure).

I have tried and failed to write a script that can do the following -

1) Compare each column of 2 tables with an identical structure

2) Determine for each record and for each column if the column value differs between the 2 tables

3) List the difference for each record and column

4) The program should be generic, i.e. if the table structure changes, I will not have to change the code. The code should be able to determine the table structure.

I did think about using "cursor". Didn't work. Not sure whether this is because cursors don't work in an AMDP or because they can't use internal tables.

Example

Table 1

Col1 Col2 Col3

A01 1 2

A02 3 4

Table 2

Col1 Col2 Col3

A01 1 2

A02 3 5

Result

Col1 Col2 Col3 Col4
(ID) (Col Name) (Value 1) (Value 2)

A02 Col3 4 5

Re: HANA SQL loop over columns

former_member302041 — Sun, 26 Aug 2018 09:21:21 GMT

Wow, very challenging requirement (especially "generic")...

In general I would try the next (it isn't a solution, but direction) in AMDP:

1. in each table concatenate all non key values (something like this)

In order to be generic we need to know what columns are in the table so we can try using "SYS"."COLUMNS"

2. join the tables and compare concatenated columns to identify rows that differ (using case/when)

3. finally, if you need to know in which column there is a difference then try to parse values by "|" and compare

It seems to be extremely complex solution (if it is possible at all).

Please share if you succeed (in any way)...

BR,

Re: HANA SQL loop over columns

lbreddemann — Mon, 27 Aug 2018 00:02:47 GMT

This is a question from the corner of "making SQL a dynamically typed language"...

I see different approaches to this problem and ways to solve some smaller problems easily, but before that, it would be good if you could explain the use case here.

Why do you want a column-based comparison over a pair of (necessarily) ordered list of rows?
And why does it need to be generic in itself?
What's speaking against generating the actual compare routines for each pair of tables?
What volume of data do you expect to be compared?
How many records do you think will be mismatches?
How often does the comparison be done and how much time can it take at max?
How often does the actual structure of the tables to be compared change? Or is it always just the table names that change?

Re: HANA SQL loop over columns

martin_chambers — Fri, 31 Aug 2018 16:12:26 GMT

Hi Andrey,

Thank you very much for your solution. In my particular situation, I already know which records are different. I need to identify the fields with different contents. Parsing does seem like a complicated way. Our current solution, is simple, but not generic. I simply write a comparison for each field. Unfortunately, this means that I have to adjust the coding if the underlying table structure changes.

By the way, a generic solution is possible in ABAP. In retrospect, I probably should have chosen that path, as the number of records is very small (1000 records). No performance issues.

Re: HANA SQL loop over columns

martin_chambers — Fri, 31 Aug 2018 16:26:05 GMT

Hi Lars,

The use case:

Data records are uploaded into a SAP Business Warehouse System.

The business department, can check and, if necessary, correct records before further processing. These manually changed records are saved in a shadow table. Later on they will overwrite the values in the original records.
Unfortunately, the ABAP program that is used to make these manual changes, does not tag the modified field.The comparison is made, so that a report can be sent to the data originators. They have to make the identical corrections in their data source. Otherwise, we will receive the same errors next month.

The volume of changed records is actually quite small, order of magnitude 1000 compared to the several 10 million records sent per month. The comparison is made for 8 paired tables (16 in total) and 10 data deliveries (countries) per month.

I'm not quite sure what you mean by "generating the actual compare routines for each pair of tables". We have now implemented a simple solution where we have coded a comparison explicitly for each field. This is done separately for each of the paired tables. This is not really what we wanted, e.g. if the table structure changes, the code will have to be adapted. Some things are easier in ABAP.

Re: HANA SQL loop over columns

lbreddemann — Sun, 02 Sep 2018 00:08:30 GMT

I meant, that you could write a program that creates the code for the "simple explicit comparison" for you based on the table structure.

If the tables change at some point in time you would just need to run this generator program to update the comparison.

Given the additional information, I'm under the impression that you've already found the optimal solution in terms of value for money. Sure, a generic record compare and report utility for arbitrary tables does sound useful, but the effort required to build this generic solution probably outweighs the benefit quite a bit.