Union of multiple Database queries with same parameters

by Code Error
Posted By: Anonymous

I am having this code and its running as expected. However I am trying to find a better way to rewrite the following query as it can be seen that the dates and account codes are repeated all the time.

The data is being extracted from 3 Databases i.e. Db1, Db2 and Db3.
The tables of each Database are similar. Even the AcctCodes to be extracted are similar.

So,I am wondering if the following code can be rewritten in few lines.

Since, the AcctCodes are similar, so adding an empty row with database name as Headers between each query helps me to identify them.

Select ‘Outlet1′,’0′,’0’ from Dummy

So if there is a better version of the following code, please let me know. Thanks.

Select 'Outlet1','0','0' from Dummy

UNION ALL

SELECT 
    T1."AcctCode",T1."AcctName", SUM(T0."Debit") - SUM(T0."Credit") as TotalBal
    FROM Db1.Table1 T0  
        INNER JOIN Db1.Table2 T1 ON T0."Account" = T1."AcctCode" 
        INNER JOIN Db1.Table3 T2 ON T0."TransId" = T2."TransId"
    WHERE 
        T1."AcctCode" in
        (
        '105004001','105005001','105006001','105007001','105008001','105009001','105104001','105105001','105106001',
        '105107001','105108001','105109001','106001001','107009001','109018001','109022001','201001001','201002001'
        )      
        AND '01.01.0001' <= '01.07.2020'  
        AND '31.03.2021' >= T0."RefDate"  
    GROUP BY T1."AcctCode",T1."AcctName"

UNION ALL

Select 'Outlet2','0','0' from Dummy

UNION ALL

SELECT 
    T1."AcctCode",T1."AcctName", SUM(T0."Debit") - SUM(T0."Credit") as TotalBal
    FROM Db2.Table1 T0  
        INNER JOIN Db2.Table2 T1 ON T0."Account" = T1."AcctCode" 
        INNER JOIN Db2.Table3 T2 ON T0."TransId" = T2."TransId"
    WHERE 
        T1."AcctCode" in
        (
        '105004001','105005001','105006001','105007001','105008001','105009001','105104001','105105001','105106001',
        '105107001','105108001','105109001','106001001','107009001','109018001','109022001','201001001','201002001'
        )      
        AND '01.01.0001' <= '01.07.2020'  
        AND '31.03.2021' >= T0."RefDate"  
    GROUP BY T1."AcctCode",T1."AcctName"

UNION ALL

Select 'Outlet3','0','0' from Dummy

UNION ALL

SELECT 
    T1."AcctCode",T1."AcctName", SUM(T0."Debit") - SUM(T0."Credit") as TotalBal
    FROM Db3.Table1 T0  
        INNER JOIN Db3.Table2 T1 ON T0."Account" = T1."AcctCode" 
        INNER JOIN Db3.Table3 T2 ON T0."TransId" = T2."TransId"
    WHERE 
        T1."AcctCode" in
        (
        '105004001','105005001','105006001','105007001','105008001','105009001','105104001','105105001','105106001',
        '105107001','105108001','105109001','106001001','107009001','109018001','109022001','201001001','201002001'
        )      
        AND '01.01.0001' <= '01.07.2020'  
        AND '31.03.2021' >= T0."RefDate"  
    GROUP BY T1."AcctCode",T1."AcctName"

I have reframed my code now and now it looks something like this.

SELECT 
    'Databse1' as DataSource,T1."AcctCode",T1."AcctName", SUM(T0."Debit") - SUM(T0."Credit") as TotalBal
    FROM Db1.Table1 T0  
        INNER JOIN Db1.Table2 T1 ON T0."Account" = T1."AcctCode" 
        INNER JOIN Db1.Table3 T2 ON T0."TransId" = T2."TransId"
    WHERE 
        T1."AcctCode" in
        (
        '105004001','105005001','105006001','105007001','105008001'
        )      
        AND '01.01.0001' <= '01.07.2020'  
        AND '31.03.2021' >= T0."RefDate"  
    GROUP BY T1."AcctCode",T1."AcctName"

UNION ALL

SELECT 
    'Database2' As DataSource,T1."AcctCode",T1."AcctName", SUM(T0."Debit") - SUM(T0."Credit") as TotalBal
    FROM Db2.Table1 T0  
        INNER JOIN Db2.Table2 T1 ON T0."Account" = T1."AcctCode" 
        INNER JOIN Db2.Table3 T2 ON T0."TransId" = T2."TransId"
    WHERE 
        T1."AcctCode" in
        (
        '105004001','105005001','105006001','105007001' ,'105008001' 
        )      
        AND '01.01.0001' <= '01.07.2020'  
        AND '31.03.2021' >= T0."RefDate"  
    GROUP BY T1."AcctCode",T1."AcctName"

UNION ALL

SELECT 
    'Database3' As DataDource,T1."AcctCode",T1."AcctName", SUM(T0."Debit") - SUM(T0."Credit") as TotalBal
    FROM Db3.Table1 T0  
        INNER JOIN Db3.Table2 T1 ON T0."Account" = T1."AcctCode" 
        INNER JOIN Db3.Table3 T2 ON T0."TransId" = T2."TransId"
    WHERE 
        T1."AcctCode" in
        (
        '105004001','105005001','105006001','105007001', '105008001'
        )      
        AND '01.01.0001' <= '01.07.2020'  
        AND '31.03.2021' >= T0."RefDate"  
    GROUP BY T1."AcctCode",T1."AcctName"

So, my question is if it’s possible to mention the AcctCode globally i.e. AcctCode would be same for all and all the sub queries will be using the same AcctCode…

Solution

Implementing this "merge results from n different DBs" is rather common.
Most of the times, this is done by means of a data warehouse.

HANA allows creating virtual tables that represent tables or views in remote systems – which is the basis for an integration scenario very popular with HANA sales folks: "…simply integrate all your DBs in HANA… no data warehouse and heavy data lifting required…"

I assume this is one of those scenarios.

So, what options are there to only have to specify the selection parameters once?

A simple approach would be to use query parameters.
This can be done either via user defined table functions or parameterized views (yes, also via calculation views and parameters, but I will skip this here).

So, with this one could write something like this:

CREATE VIEW CombinedOutletBalances 
                (startReferencePeriod NVARCHAR(10),
                 endReferencePeriod  NVARCHAR(10))
    as
    
    WITH selAcctCodes as 
       (SELECT '105004001' as "AcctCode" FROM DUMMY UNION ALL 
        SELECT '105005001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '105006001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '105007001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '105008001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '105009001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '105104001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '105105001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '105106001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '105107001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '105108001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '105109001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '106001001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '107009001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '109018001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '109022001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '201001001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '201002001' as "AcctCode" FROM DUMMY) 
       
    SELECT 
        'Outlet1' AS "DataSource", T1."AcctCode",T1."AcctName", SUM(T0."Debit") - SUM(T0."Credit") as TotalBal
    FROM Db1.Table1 T0  
        INNER JOIN Db1.Table2 T1 ON T0."Account" = T1."AcctCode" 
        INNER JOIN Db1.Table3 T2 ON T0."TransId" = T2."TransId"
        INNER JOIN selAcctCodes sac ON T1."AcctCode" = sac."AcctCode"
    WHERE 
             '01.01.0001' <= :startReferencePeriod
        AND :endReferencePeriod >= T0."RefDate"   
    GROUP BY 
            T1."AcctCode", T1."AcctName"
 UNION ALL  
    SELECT 
         'Outlet2' AS "DataSource", T1."AcctCode",T1."AcctName", SUM(T0."Debit") - SUM(T0."Credit") as TotalBal
        FROM Db2.Table1 T0  
            INNER JOIN Db2.Table2 T1 ON T0."Account" = T1."AcctCode" 
            INNER JOIN Db2.Table3 T2 ON T0."TransId" = T2."TransId"
            INNER JOIN selAcctCodes sac ON T1."AcctCode" = sac."AcctCode"
    WHERE 
             '01.01.0001' <= :startReferencePeriod
        AND :endReferencePeriod >= T0."RefDate"   
    GROUP BY 
            T1."AcctCode", T1."AcctName"
    SELECT 
        'Outlet3' AS "DataSource", T1."AcctCode",T1."AcctName", SUM(T0."Debit") - SUM(T0."Credit") as TotalBal
        FROM Db3.Table1 T0  
            INNER JOIN Db3.Table2 T1 ON T0."Account" = T1."AcctCode" 
            INNER JOIN Db3.Table3 T2 ON T0."TransId" = T2."TransId"
            INNER JOIN selAcctCodes sac ON T1."AcctCode" = sac."AcctCode"
        WHERE 
                '01.01.0001' <= :startReferencePeriod
            AND :endReferencePeriod >= T0."RefDate"  
        GROUP BY 
            T1."AcctCode", T1."AcctName";

This reduces the repetition to the minimum of what can be done in pure HANA SQL.

If the selection for the AcctCode should be more flexible then the next best option would be to fill a temporary table with the selected codes and join that instead of the common table expression.

Note that I pulled the DataSource into the actual data queries, that way the result set can still be handled in further queries and reporting tools without screwing up the result data (e.g. with the "rows in-between" approach you wouldn’t be able to correctly calculate the average any more).

Also note, that this may not be very well-performing, if the different source table really are on remote databases. You may want to test this extensively.

Answered By: Anonymous

Related Articles

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.

Leave a Reply

Your email address will not be published. Required fields are marked *