Home » SQL & PL/SQL » SQL & PL/SQL » check the integrity of a field between 3 or more tables - SQL Oracle (Oracle 11g)
check the integrity of a field between 3 or more tables - SQL Oracle [message #688073] |
Wed, 16 August 2023 08:14 |
|
Nicha
Messages: 20 Registered: March 2020
|
Junior Member |
|
|
There are 3 tables in the system, which may be more in the future, with account information whose main table is [ept01], referred to as table 1. All accounts, when created, must exist in this table as well as in the secondary tables "Table 2" with alias [ept02], and "Table 3" alias [ept03].
Below is a simplified representation of the 3 tables:
I need to build a Query that shows all the accounts that exist in the main table [ept01] but not in each of the secondary tables, and vice-versa. This Query must have a Control field that shows where the Account is missing from, as shown below:
Result Query
----------------------------------------------------------
Account_ID Control_ID Client_ID Timestamp
A3 ept_02_01 C2 yyyy-mm-d2 : The account exists in ept02 but not in ept01
A3 ept_03_01 C2 yyyy-mm-d2 : The account exists in ept03 but not in ept01
A7 ept_01_02 C3 yyyy-mm-d3 : The account exists in ept01 but not in ept02
Control_ID
Format : ept_t1_tn
where, (t1) is the table that has the account,
and (tn) has not.
Example :
ept_02_01 The account exists in ept02 but not in ept01
ept_01_03 The account exists in ept01 but not in ept03
The Query result must show all the accounts that exist in the main table [ept01],
and not in each of the secondary tables, and vice-versa, ordered by [Account_ID], [Control_ID],
as shown above. That Control Field [Control_ID] can result from a Case Statement.
The [Client_ID] and the [Timestamp] both come from the respective source tables, where the account exists.
Can anyone help please?
[Updated on: Wed, 16 August 2023 08:19] Report message to a moderator
|
|
|
|
Re: check the integrity of a field between 3 or more tables - SQL Oracle [message #688075 is a reply to message #688074] |
Wed, 16 August 2023 08:50 |
|
Nicha
Messages: 20 Registered: March 2020
|
Junior Member |
|
|
Thank you Michel for the Allert.
TEST CASE
---------------------------------------------------
CREATE TABLE ept01
(
Account_ID VARCHAR2(2) NOT NULL,
Client_ID VARCHAR2(2) NOT NULL,
Timestamp VARCHAR2(10) NOT NULL,
)
insert into ept01 values ('A1','C1','yyyy-mm-d1');
insert into ept01 values ('A2','C1','yyyy-mm-d1');
insert into ept01 values ('A4','C2','yyyy-mm-d2');
insert into ept01 values ('A5','C3','yyyy-mm-d3');
insert into ept01 values ('A7','C3','yyyy-mm-d3');
insert into ept01 values ('A8','C4','yyyy-mm-d4');
insert into ept01 values ('A9','C4','yyyy-mm-d5');
insert into ept01 values ('A10','C5','yyyy-mm-d5');
CREATE TABLE ept02
(
Account_ID VARCHAR2(2) NOT NULL,
Client_ID VARCHAR2(2) NOT NULL,
Timestamp VARCHAR2(10) NOT NULL,
)
insert into ept01 values ('A1','C1','yyyy-mm-d1');
insert into ept01 values ('A2','C1','yyyy-mm-d1');
insert into ept01 values ('A3','C2','yyyy-mm-d2');
insert into ept01 values ('A4','C2','yyyy-mm-d3');
insert into ept01 values ('A5','C3','yyyy-mm-d3');
insert into ept01 values ('A8','C4','yyyy-mm-d4');
insert into ept01 values ('A9','C4','yyyy-mm-d5');
insert into ept01 values ('A10','C5','yyyy-mm-d5');
CREATE TABLE ept03
(
Account_ID VARCHAR2(2) NOT NULL,
Client_ID VARCHAR2(2) NOT NULL,
Timestamp VARCHAR2(10) NOT NULL,
)
insert into ept01 values ('A1','C1','yyyy-mm-d1');
insert into ept01 values ('A2','C1','yyyy-mm-d1');
insert into ept01 values ('A3','C2','yyyy-mm-d2');
insert into ept01 values ('A4','C2','yyyy-mm-d3');
insert into ept01 values ('A5','C3','yyyy-mm-d3');
insert into ept01 values ('A7','C3','yyyy-mm-d3');
insert into ept01 values ('A8','C4','yyyy-mm-d4');
insert into ept01 values ('A9','C4','yyyy-mm-d5');
insert into ept01 values ('A10','C5','yyyy-mm-d5');
|
|
|
Re: check the integrity of a field between 3 or more tables - SQL Oracle [message #688076 is a reply to message #688074] |
Wed, 16 August 2023 09:06 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Assuming account_id is unique in each table:
with t as (
select account_id,
1 tbl_flag
from ept01
union all
select account_id,
2 tbl_flag
from ept02
union all
select account_id,
4 tbl_flag
from ept03
)
select case sum(tbl_flag)
when 1 then 'Account is missing in ept02 and ept03'
when 2 then 'Account is missing in ept01 and ept03'
when 4 then 'Account is missing in ept01 and ept02'
when 3 then 'Account is missing in ept 03'
when 5 then 'Account is missing in ept 02'
when 6 then 'Account is missing in ept 01'
end missing_accounts
from t
group by account_id
having sum(tbl_flag) != 7
/
SY.
P.S. change sum(tbl_flag) to sum(distinct tbl_flag) if account_id isn't unique within tables.
[Updated on: Wed, 16 August 2023 09:09] Report message to a moderator
|
|
|
Re: check the integrity of a field between 3 or more tables - SQL Oracle [message #688080 is a reply to message #688076] |
Wed, 16 August 2023 10:01 |
|
Nicha
Messages: 20 Registered: March 2020
|
Junior Member |
|
|
Thank you Solomon for your help.
As I mentioned, the number of tables will increase in future - up to 8. Using your idea what could be the best performance SQL approach?
Starting from your idea, and make a UNION with all FULL JOINs between ept01 and the secondary tables, like below (pseudo code), because I don't know how to do it??????
with t as (
select A.*
from ept01 A
Full Join
ept02 B
on A.account_id = B.account_id
union all
select A.*
from ept01 A
Full Join
ept03 B
on A.account_id = B.account_id
union all
etc..... for (ept01 with ept04; ept01 with ept05; ept01 with ept06, and finally for ept01 with ept08)
)
If this is a worst approach, can you please show me how improve yours?
My best regards
[Updated on: Wed, 16 August 2023 10:13] Report message to a moderator
|
|
|
Re: check the integrity of a field between 3 or more tables - SQL Oracle [message #688084 is a reply to message #688080] |
Wed, 16 August 2023 11:09 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Your approach results in N full joins and it always selects EPT01.* so if, for example EPT02 has ACCOUNT_ID that is not in EPT01 full join will return EPT01 columns as NULL. So you'll know account id is missing in EPT01 but you'll not know what account id is it. You need to select ACCOUNT_ID from both tables. This way not null ACCOUNT_ID in EPT01 and null ACCOUNT_ID in EPT02 means EPT02 is missing that not null ACCOUNT_ID in EPT01 and not null ACCOUNT_ID in EPT02 and null ACCOUNT_ID in EPT01 means EPT01 is missing that not null ACCOUNT_ID in EPT02. And performance wise N full joins will be less effectivs vs N union all + group by. However, adding WHERE clauses in CASE used in my solution will be very cumbersome when number of tables is 8. This can be simplified by using collections. Create type:
create or replace
type str_list
as
table of varchar2(4000)
/
Now we can do something like:
with t as (
select account_id,
cast('EPT01' as varchar2(4000)) tbl
from ept01
union all
select account_id,
'EPT02' tbl
from ept02
union all
select account_id,
'EPT03' tbl
from ept03
...
union all
select account_id,
'EPT08' tbl
from ept08
)
select account_id,
str_list('EPT01','EPT02','EPT03',...'EPT08') multiset except cast(collect(tbl) as str_list) account_id_is_missing_in
from t
group by account_id
having str_list('EPT01','EPT02','EPT03',...'EPT08') multiset except cast(collect(tbl) as str_list) != str_list()
/
Just replace elipses with your current set of table names. For example:
with t as (
select job,
cast('deptno 10' as varchar2(4000)) d
from emp
where deptno = 10
union all
select job,
'deptno 20' d
from emp
where deptno = 20
union all
select job,
'deptno 30' d
from emp
where deptno = 30
)
select job,
str_list('deptno 10','deptno 20','deptno 30') multiset except cast(collect(d) as str_list) job_is_missing_in
from t
group by job
having str_list('deptno 10','deptno 20','deptno 30') multiset except cast(collect(d) as str_list) != str_list()
/
JOB JOB_IS_MISSING_IN
--------- ----------------------------------------
ANALYST STR_LIST('deptno 10', 'deptno 30')
PRESIDENT STR_LIST('deptno 20', 'deptno 30')
SALESMAN STR_LIST('deptno 10', 'deptno 20')
SQL>
SY.
[Updated on: Wed, 16 August 2023 11:16] Report message to a moderator
|
|
|
|
Re: check the integrity of a field between 3 or more tables - SQL Oracle [message #688087 is a reply to message #688086] |
Wed, 16 August 2023 13:23 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Yes, you will have to use 8, 16, 32, 64, 129, 256 etc, (in general you use POWER(2,N - 1)) as tbl_flag if you decide to use my first solution. But number of possible tbl_flag combinations with 8 tables will be very high, so you will end up with huge number og brancches in CASE expression. That's why using collection simplifies it.
SY.
[Updated on: Wed, 16 August 2023 13:24] Report message to a moderator
|
|
|
|
|
Re: check the integrity of a field between 3 or more tables - SQL Oracle [message #688095 is a reply to message #688091] |
Wed, 16 August 2023 17:59 |
|
Nicha
Messages: 20 Registered: March 2020
|
Junior Member |
|
|
I agree with your criticism of my post. My sincere apologies.
I hoped that the image was sufficiently enlightening, also because reality is much more complex and not always possible to recreate through the CREATE and INSERT instructions. If it is not possible for you to contribute to the solution, I appreciate the criticism, and I will try to improve in future posts.
|
|
|
Goto Forum:
Current Time: Sat Jun 29 23:15:35 CDT 2024
|