I have a rather interesting problem which I would like to see if anyone can help me come up with a better solution to my written sql. I have one table which contains elements and their hierarchy nodes (acctg_table). I have another table called USER_TABLE and the acct nodes assign to that specific user. A_funding and A_organization in the acctg_table is the value I would always like to retrieve.
The problem that I'm trying to figured out is a better way to write my SQL. The user can have any hierarchy node in the acctg_table as a value in the user_table and therefore I need to check every possible combination of a_funding and a_orgn in the user_table to every possible combination on the acctg_table. That means my or statement would have to check every column for a possible match. I feel like there should be a better method to accomplish this without having all these "OR" within my SQL.
I have simplified the tables here so I can get my point across, but my real tables can have up to 8 hierarchy nodes.
User table DDL:
CREATE TABLE USER_TABLE (
USER_ID VARCHAR2(60 BYTE),
A_FUNDING VARCHAR2(8 BYTE),
A_ORGN VARCHAR2(8 BYTE)
)
Insert into USER_TABLE (USER_ID,A_FUNDING,A_ORGN) values ('BILL','140800','2046');
Insert into USER_TABLE (USER_ID,A_FUNDING,A_ORGN) values ('JHON','354001','5402');
Acctg_table DDL:
CREATE TABLE ACCTG_TABLE (
A_FUNDING VARCHAR2(8 BYTE),
A_ORGANIZATION VARCHAR2(8 BYTE),
FUNDING_LEVEL1 VARCHAR2(8 BYTE),
FUNDING_LEVEL2 VARCHAR2(8 BYTE),
FUNDING_LEVEL3 VARCHAR2(8 BYTE),
A_ORGANIZATION_LEVEL_1 VARCHAR2(8 BYTE),
A_ORGANIZATION_LEVEL_2 VARCHAR2(8 BYTE),
A_ORGANIZATION_LEVEL_3 VARCHAR2(8 BYTE)
);
Insert into ACCTG_TABLE (A_FUNDING,A_ORGANIZATION,FUNDING_LEVEL1,FUNDING_LEVEL2,FUNDING_LEVEL3,A_ORGANIZATION_LEVEL_1,A_ORGANIZATION_LEVEL_2,A_ORGANIZATION_LEVEL_3) values ('140820','3503','130000','140800','140820','01','1015','2046');
Insert into ACCTG_TABLE (A_FUNDING,A_ORGANIZATION,FUNDING_LEVEL1,FUNDING_LEVEL2,FUNDING_LEVEL3,A_ORGANIZATION_LEVEL_1,A_ORGANIZATION_LEVEL_2,A_ORGANIZATION_LEVEL_3) values ('370658','5728','309999','350000','354001','5728','5725','5402');
select
u.user_id,
a.a_funding,
a.a_organization
from acctg_table a,
user_table u
where (
a.A_FUNDING = U.A_FUNDING
and a.A_ORGANIZATION = U.A_ORGN
OR U.A_FUNDING = a.funding_level1
and U.a_orgn = a.a_organization_level_1
OR U.A_FUNDING = a.funding_level2
and U.a_orgn = a.a_organization_level_2
OR U.A_FUNDING = a.funding_level3
and U.a_orgn = a.a_organization_level_3
OR U.A_FUNDING = a.funding_level1
and U.a_orgn = a.a_organization_level_3
OR U.A_FUNDING = a.funding_level2
and U.a_orgn = a.a_organization_level_3
)
expected Result:
| Header 1 | Header 2 | Header 3 |
|---|
| BILL | 140820 | 3503 |
| JHON | 370658 | 5728 |
| | |
My database is a Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit.