none
Need help on query

    질문

  • HI,

    Using below query getting the below result table.

    select cond.project_num,cond.physical_inspect_date,deficiency_code, cond.life_safety,cond.on_prior_rep,cond.repair_time, defi.deficiency_id,cat.category_id,icat.ITEM_INSPECTED
    from AM_OI_PHY_INSP_PHY_COND cond, AM_OI_PHY_INSP_COND_DEFICIENCY defi, AM_OI_PHY_INSP_COND_CATEGORY cat, AM_OI_PHY_INSP_ITEMS_CATEGORY icat
    where cond.project_num='600002' and cond.physical_inspect_date='8-JUNE-2018' 
    and cond.deficiency_id=defi.deficiency_id and defi.category_id=cat.category_id and cat.ITEM_INSPECTED=icat.ITEM_INSPECTED


    Now i want to get one row value based on item_inspected column. so expected return result will be

    600002 6/8/2018 C * Y 8

    600002 6/8/2018  C * Y 8

    so basically C always precedence if there is C in multiple rows for the same item_inspected then return the max repair_time.

    Thanks in advanced!

    2018년 5월 18일 금요일 오전 11:41

모든 응답

  • you mean this?

    select project_num,
    physical_inspect_date,
    deficiency_code,
    life_safety,
    on_prior_rep,
    repair_time,
    deficiency_id,
    category_id,
    ITEM_INSPECTED
    from
    (
    select cond.project_num,cond.physical_inspect_date,deficiency_code, cond.life_safety,cond.on_prior_rep,cond.repair_time, defi.deficiency_id,cat.category_id,icat.ITEM_INSPECTED,
    row_number() over (partition by cond.project_num,cond.physical_inspect_date,icat.ITEM_INSPECTED ORDER BY CASE WHEN DEFICIENCY_CODE = 'C' THEN 1 ELSE 2 END,REPAIR_TIME DESC) AS Seq
    from AM_OI_PHY_INSP_PHY_COND cond, AM_OI_PHY_INSP_COND_DEFICIENCY defi, AM_OI_PHY_INSP_COND_CATEGORY cat, AM_OI_PHY_INSP_ITEMS_CATEGORY icat
    where cond.project_num='600002' and cond.physical_inspect_date='8-JUNE-2018' 
    and cond.deficiency_id=defi.deficiency_id and defi.category_id=cat.category_id and cat.ITEM_INSPECTED=icat.ITEM_INSPECTED
    )t
    where Seq = 1


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 5월 18일 금요일 오후 1:38
  • Appreciated for your help. after executing your above query getting below error.

    "

    [Error] Execution (11: 2): ORA-00911: invalid character

    "

    2018년 5월 18일 금요일 오후 2:11
  • Appreciated for your help. after executing your above query getting below error.

    "

    [Error] Execution (11: 2): ORA-00911: invalid character

    "

    The error is related ORACLE and you should try ORACLE forum to get a better answer 

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    2018년 5월 18일 금요일 오후 2:22
  • Appreciated for your help. after executing your above query getting below error.

    "

    [Error] Execution (11: 2): ORA-00911: invalid character

    "

    So you're on Oracle

    Then this is not the right forum

    You need to post this in some Oracle forums

    This being a forum fro MS SQL Server, solutions are based on T-SQL and may not work well on Oracle

    Anyways you can try this

    select cond.project_num,cond.physical_inspect_date,deficiency_code, cond.life_safety,cond.on_prior_rep,cond.repair_time, defi.deficiency_id,cat.category_id,icat.ITEM_INSPECTED
    from AM_OI_PHY_INSP_PHY_COND cond, AM_OI_PHY_INSP_COND_DEFICIENCY defi, AM_OI_PHY_INSP_COND_CATEGORY cat, AM_OI_PHY_INSP_ITEMS_CATEGORY icat
    where cond.project_num='600002' and cond.physical_inspect_date='8-JUNE-2018' 
    and cond.deficiency_id=defi.deficiency_id and defi.category_id=cat.category_id and cat.ITEM_INSPECTED=icat.ITEM_INSPECTED
    AND deficiency_code = 'C'
    AND cond.repair_time = (select max(repair_time)
    from AM_OI_PHY_INSP_PHY_COND
    where project_num = cond.project_num
    )


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    2018년 5월 18일 금요일 오후 2:23