Obtaining one description from multiple domain tables with Oracle-CodePudding

How can I merge descriptions obtained from three domain tables into a single description column?

There is a transaction table that has transaction ID's and three domain tables that between them have the descriptions for the transaction IDs - something like this:

 Transaction Table: TANS_TBL  with columns like TRANS_ID, TRANS_START_TM, TRANS_END_TM, TRANS_RESULT_CD
    Domain Table 1: DMN_TANS_DESC_TBL1  with columns like TRANS_ID, DMN1_SHORT_DESC, DMN1_LONG_DESC
    Domain Table 2: DMN_TANS_DESC_TBL2  with columns like TRANS_ID, DMN2_SHORT_DESC, DMN2_LONG_DESC
    Domain Table 3: DMN_TANS_DESC_TBL3  with columns like TRANS_ID, DMN3_SHORT_DESC, DMN3_LONG_DESC

The rows for the TRANS_ID and short descriptions are not unique in each table. A TRANS_ID may have multiple rows in a Domain Table.
Only the Short Description is needed; only one row for a TRANS_ID is wanted.
The column names for descriptions are different in each domain table.
Any given TRANS_ID will appear in only one domain table (I believe other things in the application will break if that is not true, but I don't see anything to enforce that)

Data needs to be extracted with the column headers like this:

TRANS_ID, TRANS_SHORT_DESC, TRANS_START_TM, TRANS_END_TM

No table modifications or additions are permitted.

Using this, the descriptions can be obtained:

select trns.TRANS_ID, dmn1.DMN1_SHORT_DESC, dmn2.DMN2_SHORT_DESC, dmn3.DMN3_SHORT_DESC
from TRANS_TBL trns
     left join DMN_TANS_DESC_TBL1 dmn1 ON dmn1.TRANS_ID=trns.TRANS_ID
     left join DMN_TANS_DESC_TBL2 dmn2 ON dmn2.TRANS_ID=trns.TRANS_ID
     left join DMN_TANS_DESC_TBL3 dmn3 ON dmn3.TRANS_ID=trns.TRANS_ID;

However that has two problems:

Duplicate rows for each domain table description row, and
There are three description columns, two out of three NULL, for each row

One description row from one domain table can be obtained with this:

select TRANS_ID, TRANS_DESC
from ( select dmn1.TRANS_ID, dmn1.DMN1_SHORT_DESC as "TRANS_DESC", row_number()
       over( partision by dmn1.TRANS_ID ORDER by dmn1.TRANS_ID) as row_num
       from DM_TANS_DESC_TBL1 dmn1
     )
where row_num=1;

But I haven't found a way to bring those descriptions from the 3 domain tables into a single transaction ID description column.

CodePudding user response：

You can simply use COALESCE or nested NVL or a DECODE or a CASE statement to combine the columns into one. Something like:

SELECT trans_id,
       trans_desc
  FROM (SELECT trans_id,
               trans_desc,
               ROW_NUMBER() OVER (PARTITION BY trans_id ORDER BY DECODE(trans_desc,NULL,2,1) ASC, trans_id DESC) seq
          FROM (SELECT trans_tbl.trans_id,
                       COALESCE(dmn1.dmn1_short_desc,dmn2.dmn2_short_desc,dmn3.dmn3_short_desc) trans_desc
                  FROM trans_tbl
                       left join DMN_TANS_DESC_TBL1 dmn1 ON dmn1.TRANS_ID=trns.TRANS_ID
                       left join DMN_TANS_DESC_TBL2 dmn2 ON dmn2.TRANS_ID=trns.TRANS_ID
                       left join DMN_TANS_DESC_TBL3 dmn3 ON dmn3.TRANS_ID=trns.TRANS_ID))
 WHERE seq = 1

The DECODE in the ROW_NUMBER logic is in order to prefer non-null values over null values.

CodePudding user response：

With your sample data something like here:

WITH
    tbl (TRANS_ID, TRANS_START_TM, TRANS_END_TM) AS
        (
            Select 1, '09:00:00' , '09:01:00'   From Dual Union All
            Select 2, '09:12:00' , '09:15:00'   From Dual Union All
            Select 3, '09:16:00' , '09:17:00'   From Dual Union All
            Select 4, '09:21:00' , '09:22:00'   From Dual Union All
            Select 5, '09:23:00' , '09:27:00'   From Dual 
        ),
    desc_tbl_1 (TRANS_ID, DMN1_SHORT_DESC) AS
        (
            Select  1, 'D1 T1 - some desc'     From Dual Union All
            Select  1, 'D1 T1 - some desc'     From Dual 
        ),
    desc_tbl_2 (TRANS_ID, DMN2_SHORT_DESC) AS
        (
            Select  2, 'D2 T2 - some desc'     From Dual Union All
            Select  2, 'D2 T2 - some desc'     From Dual Union All
            Select  3, 'D2 T3 - some desc'     From Dual 
        ),
    desc_tbl_3 (TRANS_ID, DMN3_SHORT_DESC) AS
        (
            Select  4, 'D3 T4 - some desc'     From Dual Union All
            Select  5, 'D3 T5 - some desc '    From Dual
        ),

... you could create a CTE descriptions to colect them in one column

    descriptions (TRANS_ID, TRANS_SHORT_DESC, RN) AS
        (
            Select TRANS_ID, DMN1_SHORT_DESC, ROW_NUMBER() OVER(Partition By TRANS_ID Order By 1) From desc_tbl_1 Union All
            Select TRANS_ID, DMN2_SHORT_DESC, ROW_NUMBER() OVER(Partition By TRANS_ID Order By 1) From desc_tbl_2 Union All
            Select TRANS_ID, DMN3_SHORT_DESC, ROW_NUMBER() OVER(Partition By TRANS_ID Order By 1) From desc_tbl_3 
        )

Main SQL

Select      t.TRANS_ID, d.TRANS_SHORT_DESC, t.TRANS_START_TM, t.TRANS_END_TM 
From        tbl t
Inner Join  descriptions d ON(d.TRANS_ID = t.TRANS_ID And d.RN = 1)

Result:

TRANS_ID	TRANS_SHORT_DESC	TRANS_START_TM	TRANS_END_TM
1	D1 T1 - some desc	09:00:00	09:01:00
2	D2 T2 - some desc	09:12:00	09:15:00
3	D2 T3 - some desc	09:16:00	09:17:00
4	D3 T4 - some desc	09:21:00	09:22:00
5	D3 T5 - some desc	09:23:00	09:27:00

NOTE - If there is a possibility that some ID has no description from 3 domains then use Left Join and handle null value.