How do I create an Intersect Query similar to Oracle's?

P

pjdavila

Below is an excerpt of the Oracle script:
select s.proj_short_name as ProjNo,
t.proj_id as ProjID,
w.wbs_name as ProjName,
c.proj_catg_name as PM,
t.task_code as ActvID,
substr(t.task_type,4,100) as ActvType,
substr(t.status_code,4,100) as Status,
t.task_name as ActvName,
round((t.TARGET_DRTN_HR_CNT/8),0) as Dur,

decode(t.act_start_date,'',to_char(trunc(t.target_start_date,'DD')),to_char(trunc(t.act_start_date,'DD'))) as Startt,

decode(t.act_end_date,'',to_char(trunc(t.target_end_date,'DD')),to_char(trunc(t.act_end_date,'DD'))) as Finish,
nvl(a.task_code,'') as PredID,
nvl(a.task_name,'') as PredName,

decode(a.act_start_date,'',to_char(trunc(a.target_start_date,'DD')),to_char(trunc(a.act_start_date,'DD'))) as PredSta,

decode(a.act_end_date,'',to_char(trunc(a.target_end_date,'DD')),to_char(trunc(a.act_end_date,'DD'))) as PredFin
from taskpred p,
task t,
task a,
project s,
projwbs w,
projpcat b,
pcatval c
where t.proj_id=p.proj_id
and t.task_id=p.task_id
and a.proj_id=t.proj_id
and a.proj_id=p.pred_proj_id
and a.task_id=p.pred_task_id
and p.proj_id=s.proj_id
and t.proj_id=s.proj_id
and s.proj_id=w.proj_id
and s.proj_short_name=w.wbs_short_name
AND s.proj_short_name IS NOT NULL
AND s.proj_short_name NOT LIKE '%-%'
AND s.proj_short_name NOT LIKE '%.%'
AND(INSTR(TRANSLATE(s.proj_short_name,
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X') = 0)
and substr(p.pred_type,4,100) in ('FF')
and substr(t.status_code,4,100) in ('Active','NotStart')
and b.proj_catg_id=c.proj_catg_id
and w.proj_node_flag='Y'
and substr(w.status_code,4,20)='Open'
and b.proj_catg_type_id=28
and w.proj_id=b.proj_id
intersect
select s.proj_short_name as ProjNo,
t.proj_id as ProjID,
w.wbs_name as ProjName,
c.proj_catg_name as PM,
t.task_code as ActvID,
substr(t.task_type,4,100) as ActvType,
substr(t.status_code,4,100) as Status,
t.task_name as ActvName,
round((t.TARGET_DRTN_HR_CNT/8),0) as Dur,

decode(t.act_start_date,'',to_char(trunc(t.target_start_date,'DD')),to_char(trunc(t.act_start_date,'DD'))) as Startt,

decode(t.act_end_date,'',to_char(trunc(t.target_end_date,'DD')),to_char(trunc(t.act_end_date,'DD'))) as Finish,
nvl(a.task_code,'') as PredID,
nvl(a.task_name,'') as PredName,

decode(a.act_start_date,'',to_char(trunc(a.target_start_date,'DD')),to_char(trunc(a.act_start_date,'DD'))) as PredSta,

decode(a.act_end_date,'',to_char(trunc(a.target_end_date,'DD')),to_char(trunc(a.act_end_date,'DD'))) as PredFin
from taskpred p,
task t,
task a,
project s,
projwbs w,
projpcat b,
pcatval c
where t.proj_id=p.proj_id
and t.task_id=p.task_id
and a.proj_id=t.proj_id
and a.proj_id=p.pred_proj_id
and a.task_id=p.pred_task_id
and p.proj_id=s.proj_id
and t.proj_id=s.proj_id
and s.proj_id=w.proj_id
and s.proj_short_name=w.wbs_short_name
AND s.proj_short_name IS NOT NULL
AND s.proj_short_name NOT LIKE '%-%'
AND s.proj_short_name NOT LIKE '%.%'
AND(INSTR(TRANSLATE(s.proj_short_name,
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X') = 0)
and substr(p.pred_type,4,100) in ('SS')
and substr(t.status_code,4,100) in ('Active','NotStart')
and b.proj_catg_id=c.proj_catg_id
and w.proj_node_flag='Y'
and substr(w.status_code,4,20)='Open'
and b.proj_catg_type_id=28
and w.proj_id=b.proj_id
order by 1,5,12
 
Top