Query to use the same alias for multiple columns when returning results from two Oracle tables-CodePudding

I am querying two tables, one is current data and the other has historical data. The data in the two tables is the same but the column names are different. Each time my where condition is met in either table, I want it to be displayed in its own row using the same alias I define regardless of the table it's found in.

The GROUP_NUM value is the same across both tables and all other data is unique. I want the GROUP_NUM value to be repeated based on the number of times it's found in either table and then display the unique values in the remaining 3 columns I've defined.

I believe I may need to use a subquery, but am at a loss on how to write that. Any guidance you can provide would be sincerely appreciated.

Here is the query I have now:

SELECT TRIM(CT.GROUP_SEQ) AS GRP_NUM,
CT.CONTRACT AS CONT_CD,
CH.H_CONT AS CONT_CD,
CT.CONTRACT_EFF AS CONT_EFF_DT,
CH.HST_EFF AS CONT_EFF_DT,
CH.HST_END_DTE AS CONT_END_DT
FROM GRPTBL GR 
INNER JOIN CURRCNT CT ON CT.GROUP_SEQ = GR.GROUP_SEQ
LEFT JOIN HISTCNT CH ON GR.GROUP_SEQ = CH.GROUP_SEQ
where TRIM(CASE_NBR) = 'DEF001'
and gs_hst_eff_dte <> gs_hst_end_dte

These are the results that I'm getting with my query:

GRP_NUM      CONT_CD CONT_CD_1  CONT_EFF_DT CONT_EFF_DT_1 CONT_END_DT
DEF001Z001   77KH    6BXD       20230101    20220101      20230101
DEF001Z002   77KH    6BXD       20230101    20220101      20230101
DEF001Z003   6UZ3    52FL       20230101    20220101      20230101
DEF001Z004   6UZ3               20230101        
DEF001Z005   77KL    6BXC       20230101    20220101      20230101
DEF001Z006   77KL    6BXC       20230101    20220101      20230101

This is how I need it to be returned:

GRP_NUM     CONT_CD CONT_EFF_DT CONT_END_DT
DEF001Z001  77KH    20230101    
DEF001Z001  6BXD    20220101    20230101
DEF001Z002  77KH    20230101    
DEF001Z002  6BXD    20220101    20230101
DEF001Z003  6UZ3    20230101    
DEF001Z003  52FL    20220101    20230101
DEF001Z004  6UZ3    20230101

CodePudding user response：

The way you described it, it is not a join nor a subquery, but union.

I don't have your tables so I'm not sure whether I guessed it right, but - that's the general idea:

-- values from CURRCNT table (joined to GRPTBL)
SELECT TRIM (gr.group_seq) grp_num,
       ct.contract cont_cd,
       ct.contract_eff cont_eff_dt,
       NULL cont_end_dt
  FROM grptbl gr JOIN currcnt ct ON ct.group_seq = gr.group_seq
 WHERE     TRIM (case_nbr) = 'DEF001'
       AND gs_hst_eff_dte <> gs_hst_end_dte
UNION
-- values from HISTCNT table (joined to GRPTBL)
SELECT TRIM (gr.group_seq) grp_num,
       ch.h_cont cont_cd,
       ch.hst_eff cont_eff_dt,
       ch.hst_end_dte cont_end_dt
  FROM grptbl gr JOIN histcnt ch ON ch.group_seq = gr.group_seq
 WHERE     TRIM (case_nbr) = 'DEF001'
       AND gs_hst_eff_dte <> gs_hst_end_dte;