-
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
-
Did you try it "as is"?
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:
Code:
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
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
Geez... that's all it was.. wow thanks. Ran perfectly. I need more practice with this. :/
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|