For a specific requirement I have to prepare a Oracle SQL, below are the tables and their relations and required data scheme:

I can get the required data by joining the individual tables with the Context table and then using Union All, but due to some constrain I can use only where clause here. Please suggest if I can get the same data by where clause.

CodePudding user response：

An exotic way to get UNION ALL without UNION

with t1(id,a) as
( 
  SELECT 1,'A' FROM DUAL UNION ALL
  SELECT 2,'B' FROM DUAL UNION ALL
  SELECT 3,'C' FROM DUAL 
),
t2(id,b) as
( 
  SELECT 1,'X' FROM DUAL UNION ALL
  SELECT 2,'Y' FROM DUAL UNION ALL
  SELECT 3,'Z' FROM DUAL 
)
select coalesce(t1.id, t2.id) id, a, b
from t1
full join t2 on 1=2
order by coalesce(t1.id, t2.id), a, b

CodePudding user response：

If system views are allowed

select 
  cxt.context_id, cxt.context_name
, job.Job_cxt_id, job.Job_name
, loc.loc_cxt_id, loc.geo_id
from Context_table cxt
left join ALL_TABLES tbl
  on tbl.table_name in ('JOB_CONTEXT_TABLE', 'LOCATION_CONTEXT_TABLE')
left join Job_context_table job
  on job.context_id = cxt.context_id
 and tbl.table_name like 'JOB%'
left join location_context_table loc
  on loc.context_id = cxt.context_id
 and tbl.table_name like 'LOC%'
order by tbl.table_name

If not, then

select 
  nvl(cxt1.context_id, cxt2.context_id) as context_id
, nvl(cxt1.context_name, cxt2.context_name) as context_name
, job.Job_cxt_id, job.Job_name
, loc.loc_cxt_id, loc.geo_id
from Context_table cxt1
full join Context_table cxt2 on -0!=0
left join Job_context_table job 
  on job.context_id = cxt1.context_id
left join location_context_table loc
  on loc.context_id = cxt2.context_id
order by cxt1.context_id