Hierarchical query with some joins

I am struggling to write a performing query which would consist of the data from one sub-select, and hierarchically retrieved data from another table based on the rows from that first sub-select.

So, I have some data retrieved from multiple tables with joins, which finally boils down to the following:

CREATE TABLE TBL1 (UUID, MiscData, KeyToLookup, ConditionClause ) AS
SELECT  13, 'ATM',             12345, null                  FROM DUAL UNION ALL
SELECT 447, 'Balance Inquiry', 67890, 'BALANCE_INQUIRY_FEE' FROM DUAL UNION ALL
SELECT 789, 'Credit',          22321, 'CREDIT_FEE'          FROM DUAL;

Now, I have another table which stores the hierarchical structure of fees:

CREATE TABLE TBL2 ( TariffDomainID, FeeType, UpperTariffDomainID, ID ) AS
SELECT 1543, 'WHATEVER_FEE',         154, 1 FROM DUAL UNION ALL 
SELECT 1543, 'BALANCE_INQUIRY_FEE',  154, 2 FROM DUAL UNION ALL
SELECT  154, 'SMTHELSE_FEE',          15, 3 FROM DUAL UNION ALL
SELECT  154, 'CREDIT_FEE',            15, 4 FROM DUAL UNION ALL
SELECT   15, 'BALANCE_INQUIRY_FEE', null, 5 FROM DUAL;

And there is a way to link the first selection to the lowest row in hierarchy of the second table, there are few joins but finally it's like this:

CREATE TABLE TBL3 ( ID, FirstTblKey, SecondTblKey ) AS
SELECT 1, 67890, 1543 FROM DUAL UNION ALL
SELECT 2, 22321, 1543 FROM DUAL;

The important point is that it's not guaranteed there will be a row with this KeyToLookup directly in the second table, as directed by the TBL3. E.g. in the example above:

row TBL1.UUID=789 is linked via TBL3 to TBL2 row with TariffDomainID=1543,
but there is no row in TBL2 with TariffDomainID=1543 and FeeType=CREDIT_FEE;

however TBL2 contains a link to the same table but upper level, UpperTariffDomainID=154,
and there is a row in TBL2 with TariffDomainID=154 and FeeType=CREDIT_FEE.

In the end I need to connect the info from TBL1 with the all occurrences of this key in TBL2 hierarchically, numerated by depth of hierarchy.

So I expect to get this:

| UUID |     MiscData    | KeyToLookup |   ConditionClause   | TariffDomainIDWithPresence | Depth |
|------|-----------------|-------------|---------------------|----------------------------|-------|
| 13   | ATM             |    12345    | null                |            null            |  null |
| 447  | Balance Inquiry |    67890    | BALANCE_INQUIRY_FEE |            1543            |   1   |
| 447  | Balance Inquiry |    67890    | BALANCE_INQUIRY_FEE |            15              |   3   |
| 789  | Credit          |    22321    | CREDIT_FEE          |            154             |   2   |

Could anyone please teach me how to make such a hierarchical query?

Solution

You can use a hierarchical query joined to the other two tables:

SELECT DISTINCT
       t1.uuid,
       t1.miscdata,
       t1.keytolookup,
       t1.conditionclause,
       t2.tariffdomainid,
       t2.depth
FROM   tbl1 t1
       LEFT OUTER JOIN tbl3 t3
       ON ( t1.keytolookup = t3.firsttblkey )
       OUTER APPLY (
         SELECT tariffdomainid,
                LEVEL AS depth
         FROM   tbl2 t2
         WHERE  t2.tariffdomainid = t3.secondtblkey
         START WITH
                t2.feetype = t1.conditionclause
         CONNECT BY
                PRIOR TariffDomainID = UpperTariffDomainID
       ) t2
ORDER BY
       uuid,
       depth

Which, for the sample data, outputs:

UUID | MISCDATA        | KEYTOLOOKUP | CONDITIONCLAUSE     | TARIFFDOMAINID | DEPTH
---: | :-------------- | ----------: | :------------------ | -------------: | ----:
  13 | ATM             |       12345 | null                |           null |  null
 447 | Balance Inquiry |       67890 | BALANCE_INQUIRY_FEE |           1543 |     1
 447 | Balance Inquiry |       67890 | BALANCE_INQUIRY_FEE |           1543 |     3
 789 | Credit          |       22321 | CREDIT_FEE          |           1543 |     2

(Note: you need the DISTINCT as there are multiple 1543 and 154 entries in TBL2 so the hierarchical query can take multiple paths to get from the start to the end condition. If your actual data does not have these duplicates then you should be able to remove the DISTINCT clause.)

db<>fiddle here