Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2009
    Posts
    1

    Unanswered: ANSI SQL to Oracle Old SQL conversion

    Below is the query. I need help to convert it to Oracle SQL with Inline views.
    Left Outer joins need to be converted to inline views and table names in from and where clause as needed.
    This query and new one should return same resultset

    --------------------------------------Query Start-------------------
    SELECT COUNT(*)
    FROM
    (
    SELECT
    'LeClaire' as CTO,
    da.Direct,
    da.Director,
    da.Manager,
    da.SubArea,
    da.Project,
    da.Project_Name,
    da.Project_Class,
    da.HISL,
    da.Resource_Name,
    da.Resource_Status,
    da.mon,
    to_char(sysdate, 'dd-Mon-YYYY') AS "Current_Date",
    DECODE(da.Project, NULL, 0, round(da.Slice / da.month_total, 2)) as "Approved_Demand",
    SUM(da.Availability) as "Headcount"
    FROM
    (
    SELECT
    w.level4_name AS Direct,
    w.level5_name AS Director,
    w.level6_name AS Manager,
    w.level7_name AS SubArea,
    INV.Code as Project,
    inv.name as Project_Name,
    det.hum_project_gate as Project_Class,
    r.id AS HISL,
    r.full_name as Resource_Name,
    lookup.lookup_code as Resource_Status,
    alc.slice AS Slice,
    alc.slice_date as Mon,
    avl.slice AS month_total,
    alc.slice / avl.slice as FTE,
    count(distinct r.id) AS Availability
    FROM
    clarity.nbi_dim_obs w,
    clarity.prj_blb_slices_m_avl avl,
    clarity.prj_obs_units obs,
    clarity.cmn_lookups lookup,
    clarity.srm_resources r /*clarity.odf_ca_project det,*/
    --************** Section to be Converted ***************************------------
    ----------------------------Start-----------------------------------------------------
    left outer join(clarity.prj_resources t inner join clarity.srm_resources res on t.prprimaryroleid = res.id) on r.id = t.prid
    left outer join(clarity.prj_blb_slices_m_alc alc
    left outer join(clarity.prteam tm
    inner join(clarity.inv_investments INV inner join clarity.odf_ca_project det on det.id = inv.id and det.hum_project_gate = 'approved_for_development') on tm.prprojectid = INV.ID
    and INV.Is_Active = 1) on alc.prj_object_id = tm.prid
    and alc.investment_id = inv.id) on alc.resource_id = t.prid
    --------------------------------------End------------------------------------------------
    -- inner join clarity.prj_blb_slices_m_avl avl on alc.resource_id = avl.prj_object_id
    -- inner join clarity.prj_obs_units obs on res.unique_name = obs.unique_name
    -- inner join clarity.nbi_dim_obs w on w.level7_unit_id = obs.id

    WHERE
    w.obs_type_name = 'Workgroup'
    AND alc.slice > 0
    AND alc.slice_date = avl.slice_date
    AND r.is_active = 1
    AND r.person_type = lookup.id
    AND w.level7_unit_id = obs.id
    AND alc.resource_id = avl.prj_object_id
    AND res.unique_name = obs.unique_name
    --and det.id = inv.id
    -- and w.level6_name = 'Douglas Scobee' --Test by Mike Lidquist; Commented on his reuest
    -- and rownum < 10000 --/* for test; Should be commented out for real query production run*/
    --and det.hum_project_gate = 'approved_for_development'
    GROUP BY
    w.level4_name
    , w.level5_name
    , w.level6_name
    , w.level7_name
    , r.id
    -- , prnt.prcategory
    , r.full_name
    , lookup.lookup_code
    , inv.code
    , inv.NAME
    , det.hum_project_gate
    , alc.slice_date
    , alc.slice
    , avl.slice
    ) DA

    GROUP BY
    da.direct
    , da.director
    , da.manager
    , da.subarea
    , da.project_class
    , da.hisl
    , da.resource_name
    , da.resource_status
    , da.project
    , da.Project_Name
    , da.mon
    , da.availability
    , da.slice
    , da.month_total

    ORDER BY
    da.direct
    , da.director
    , da.manager
    , da.subarea

    )

    -------------------------Query End-----------------------------

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    What version of Oracle? Your SQL appears to be valid in Oracle 9 and later versions.

    In older versions you can use the (+) operator to get the equivalent of an outer join.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •