convert db2 query to oracle
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: convert db2 query to oracle

  1. #1
    Join Date
    May 2010
    Posts
    2

    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

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,470

    Cool 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

  3. #3
    Join Date
    May 2010
    Posts
    2
    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
  •  



Click Here to Expand Forum to Full Width