How to search one record in many tables in HANA

[nirmal_mathew]

Hi All,

I have 20 tables under one schema. EBELN and EBELP are the primary keys for every 20 tables. My requirement is to build a HANA view, which goes through all the 20 tables and produce an output if the record exist or not in each of the 20 tables.

For example if my record has EBELN = 400098 and EBELP = 001 and my tables are in the sequence X1,...X20

Desired Output:

X1 : The EBELN||EBELP combination does not exist

X2: The EBELN||EBELP combination exist

.

.

.

X20: The EBELN||EBELP combination does not exist.

I can individually write query for each table and check. But is there any way I can build a view to check for all the 20 tables and produce a result as above?

Thanks

[lbreddemann · Accepted solution]

There is no single command that looks into a list of tables.

You will have to build such a data access.

The most straightforward way would be to write a SQL statement that is fixed for the data you are looking for. Since this is not particularly useful for the next EBELN/EBELP values, a more dynamic approach would involve using SQL Script and variables. In case you are using a host programming language (JAVA, C, ABAP), you can put this variable simply into a prepared statement and get the same result without SQLScript.

A simple example:

create column table x1 as (select user_name from users );
create column table x2 as (select user_name from users );
create column table x3 as (select user_name from users );

insert into x2 values ('LARS');
insert into x3 values ('LARS');

do begin
declare search_name nvarchar(256) ='LARS';

              select 'x1' as src, count(*) cnt from x1 where user_name = :search_name
    union all select 'x2' as src, count(*) cnt from x2 where user_name = :search_name
    union all select 'x3' as src, count(*) cnt from x3 where user_name = :search_name;
end;

From here you should be able to extend to your use case and output preferences.

As a recommendation, I like to add that you should not try to make the solution to this overly dynamic. Typing this in for twenty tables is done very quickly and results in a simple to maintain setup. Throwing in cursors and loops or more complicated dynamic programming approaches will likely take a lot more time and "bite" you later when trying to maintain the solution.