convert db2 query to oracle

[cyndicaro76]

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

[LKBrwn_DBA]

Please use the code tags to format your queries.
By removing the "Reply with quote" (which I do not know what it is used for) and the "AS" from {query} AS {alias}, the rest of the code should work fine as is:

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) 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) hs2
            WHERE hs1.jobcode = hs2.jobcode AND hs1.effdt = hs2.effdt) HS
       ON HS.jobcode = emp2.jobcode
--Reply WITH Quote

[cyndicaro76]

Geez... that's all it was.. wow thanks. Ran perfectly. I need more practice with this. :/