Append date from another table without joining-CodePudding

I have two tables in oracle that has data for different years.

Table 1: This has data for years 2016 - 2018. This is a static table ie wont be refreshed again. Table 2: This table has data for years >= 2019. This table is being refreshed 3 times a week and has a column REFRESH_DATE that contains the last refreshed date.

Now I have to do a union of table 1 and table 2 but since table 1 is static how do I take table 2's refresh date to table 1?

For example 1) Current Scenario:

with tABLE_1 as

( select  '99WC2008' as pol_id, to_date( '1/23/2016','mm/dd/yyyy') as Eff_dt , 'RETAIL' as industry , '-1' as Model from dual Union all
select  '96WC2003' as pol_id,  to_date('1/22/2016','mm/dd/yyyy') as Eff_dt , 'RETAIL' as industry , '0.34' as Model from dual Union all
select  '90WC2003' as pol_id,  to_date('1/21/2017','mm/dd/yyyy') as Eff_dt , 'RETAIL' as industry , '0.55' as Model from dual Union all
select  '10XYZ23' as pol_id,  to_date('1/12/2017','mm/dd/yyyy') as Eff_dt , 'TECH' as industry , '0' as Model from dual Union all
select  '09XYZ23' as pol_id,  to_date('1/11/2018','mm/dd/yyyy') as Eff_dt , 'TECH' as industry , '0.3' as Model from dual)
,
TABLE_2 AS

( select  'XWG5564' as pol_id, to_date( '1/23/2019','mm/dd/yyyy') as Eff_dt , 'RETAIL' as industry , '-1' as Model, to_date( '2/08/2022','mm/dd/yyyy') as REFRESH_DATE from dual Union all
select  'TTFG556' as pol_id,  to_date('1/22/2020','mm/dd/yyyy') as Eff_dt , 'RETAIL' as industry , '0.34' as Model, to_date( '2/08/2022','mm/dd/yyyy') as REFRESH_DATE from dual Union all
select  'TTH676Y' as pol_id,  to_date('1/21/2021','mm/dd/yyyy') as Eff_dt , 'RETAIL' as industry , '0.55' as Model, to_date( '2/08/2022','mm/dd/yyyy') as REFRESH_DATE from dual Union all
Select  'DFE5756' as pol_id,  to_date('2/01/2022','mm/dd/yyyy') as Eff_dt , 'TECH' as industry , '0' as Model, to_date( '2/08/2022','mm/dd/yyyy') as REFRESH_DATE from dual Union all
select  '567HNG6' as pol_id,  to_date('2/08/2022','mm/dd/yyyy') as Eff_dt , 'TECH' as industry , '0.3' as Model, to_date( '2/08/2022','mm/dd/yyyy') as REFRESH_DATE from dual)

SELECT * FROM 
(
select pol_id,  Eff_dt, industry, model  from TABLE_1
UNION ALL
select pol_id,  Eff_dt, industry, model  from TABLE_2
)
ORDER BY EFF_DT DESC

2) Required_output: Always taking the Refresh date from Table_2 and display it in Table_1 as well.

    with tABLE_1 as
    
    ( select  '99WC2008' as pol_id, to_date( '1/23/2016','mm/dd/yyyy') as Eff_dt , 'RETAIL' as industry , '-1' as Model from dual Union all
    select  '96WC2003' as pol_id,  to_date('1/22/2016','mm/dd/yyyy') as Eff_dt , 'RETAIL' as industry , '0.34' as Model from dual Union all
    select  '90WC2003' as pol_id,  to_date('1/21/2017','mm/dd/yyyy') as Eff_dt , 'RETAIL' as industry , '0.55' as Model from dual Union all
    select  '10XYZ23' as pol_id,  to_date('1/12/2017','mm/dd/yyyy') as Eff_dt , 'TECH' as industry , '0' as Model from dual Union all
    select  '09XYZ23' as pol_id,  to_date('1/11/2018','mm/dd/yyyy') as Eff_dt , 'TECH' as industry , '0.3' as Model from dual)
    ,
    TABLE_2 AS
    
    ( select  'XWG5564' as pol_id, to_date( '1/23/2019','mm/dd/yyyy') as Eff_dt , 'RETAIL' as industry , '-1' as Model, to_date( '2/08/2022','mm/dd/yyyy') as REFRESH_DATE from dual Union all
    select  'TTFG556' as pol_id,  to_date('1/22/2020','mm/dd/yyyy') as Eff_dt , 'RETAIL' as industry , '0.34' as Model, to_date( '2/08/2022','mm/dd/yyyy') as REFRESH_DATE from dual Union all
    select  'TTH676Y' as pol_id,  to_date('1/21/2021','mm/dd/yyyy') as Eff_dt , 'RETAIL' as industry , '0.55' as Model, to_date( '2/08/2022','mm/dd/yyyy') as REFRESH_DATE from dual Union all
    Select  'DFE5756' as pol_id,  to_date('2/01/2022','mm/dd/yyyy') as Eff_dt , 'TECH' as industry , '0' as Model, to_date( '2/08/2022','mm/dd/yyyy') as REFRESH_DATE from dual Union all
    select  '567HNG6' as pol_id,  to_date('2/08/2022','mm/dd/yyyy') as Eff_dt , 'TECH' as industry , '0.3' as Model, to_date( '2/08/2022','mm/dd/yyyy') as REFRESH_DATE from dual)
    
    SELECT * FROM 
    (
    select pol_id,  Eff_dt, industry, model ,  to_date( '2/08/2022','mm/dd/yyyy') as REFRESH_DATE from TABLE_1
    UNION ALL
    select pol_id,  Eff_dt, industry, model ,REFRESH_DATE  from TABLE_2
    )
    ORDER BY EFF_DT DESC

Is it possible to get data from another table without joining? In the above Union step i am hardcoding the date for table_1, the requirement is to automatically pull that date from table_2.

CodePudding user response：

Since all the REFRESH_DATEs are identical, you can use:

SELECT pol_id,
       Eff_dt,
       industry,
       model,
       (SELECT REFRESH_DATE FROM table_2 WHERE ROWNUM = 1) as REFRESH_DATE
from   TABLE_1
UNION ALL
SELECT pol_id,
       Eff_dt,
       industry,
       model,
       REFRESH_DATE
from   TABLE_2
ORDER BY EFF_DT DESC

Alternatively, you could use FETCH FIRST ROW ONLY or MAX(refresh_date).

db<>fiddle here

CodePudding user response：

Another option is to get a null refresh date from table 1 branch of the union, as a placeholder; and then in the outer query, use an analytic max():

max(refresh_date) over ()

... to get the highest value across that combined data set - i.e., the date from the table 2 rows.

SELECT pol_id, eff_dt, industry, model,
  max(refresh_date) over () as refresh_date
FROM 
(
  select pol_id,  Eff_dt, industry, model, null as refresh_date from TABLE_1
  UNION ALL
  select pol_id,  Eff_dt, industry, model, refresh_date from TABLE_2
)

db<>fiddle

You probably won't gain any real advantage from not hitting table 2 a second time, since you're already retrieving all the data from it; and any gain could be outweighed by the cost of the analytic call. You might still find one approach is marginally faster than the other - I suspect you'd have to look quite hard to notice though.