convert db2 query to oracle
Hello, i need some help converting a query to oracle...
select distinct
'Headcount'
,emp1.jobcode|| ' (Job Code)'
,emp1.jobcode|| ' - ' || JC.Descr
,'L'
,''
,''
from psoft.ps_job emp1
left outer join (Select
distinct
jc1.jobcode
,jc1.descr
From
psoft.PS_JOBCODE_TBL jc1,
(select jobcode, max(effdt) as effdt
from psoft.PS_JOBCODE_TBL
group by jobcode) as JC2
Where
jc1.jobcode= jc2.jobcode
and jc1.effdt = jc2.effdt) JC
on
jc.jobcode = emp1.jobcode
where 1=1
union
Select distinct
emp2.jobcode|| ' (Job Code)'
,emp2.jobcode || '-' || emp2.full_part_time
,''
,'L'
,emp2.full_part_time
,HS.flsa_status
From
psoft.ps_job emp2
left outer join (Select
distinct
hs1.jobcode
,hs1.flsa_status
From
psoft.PS_JOBCODE_TBL hs1,
(select jobcode, max(effdt) as effdt
from psoft.PS_JOBCODE_TBL
group by jobcode) as hs2
Where
hs1.jobcode= hs2.jobcode
and hs1.effdt = hs2.effdt) HS
on
HS.jobcode = emp2.jobcode