Union of multiple Database queries with same parameters
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
Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.