Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Join dependent on column which contains values.

mlov83Dec 13 2017 — edited Dec 14 2017

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 1Header 2Header 3
BILL1408203503
JHON3706585728

My database is a Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit.

This post has been answered by mathguy on Dec 13 2017
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 10 2018
Added on Dec 13 2017
11 comments
513 views