Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL Query GROUP BY with Subquery

2795332Apr 19 2017 — edited Apr 20 2017

Hi,

Could you tell me how to GROUP BY (ACCOUNT_DESCRIPTION) in below query.

select  DECODE (fah.asset_type, 'NATU', fab.asset_cost_acct,fab.cip_cost_acct)  asset_cost_acct

    --   ,fah.ASSET_ID

       ,(SELECT ffvv.description

   FROM fnd_flex_value_sets ffvs

       ,fnd_flex_values_vl  ffvv

  WHERE ffvs.flex_value_set_id=ffvv.flex_value_set_id

    AND ffvs.flex_value_set_name = 'AP_GL_ACCOUNT'

    AND ffvv.enabled_flag = 'Y'

    AND ffvv.flex_value = DECODE (fah.asset_type, 'NATU', fab.asset_cost_acct,fab.cip_cost_acct)

    AND sysdate BETWEEN NVL(ffvv.start_date_active,sysdate) AND NVL(ffvv.end_date_active,sysdate)) ACCOUNT_DESCRIPTION

         ,(fl.segment1||'.'||fl.segment2||'.'||fl.segment3||'.'||fl.segment4||'.'||fl.segment5) location

       ,SUM((select distinct(original_cost)

         from fa_books

         where ASSET_ID = fah.ASSET_ID

         and BOOK_TYPE_CODE = 'IBM CORP')) BEGINNING_ASSET_BALANCE

from fa_category_books fab,

     fa_asset_history  fah,

     fa_distribution_history fdh,

     fa_locations            fl 

where 1=1

and fab.BOOK_TYPE_CODE = 'IBM CORP'

and fdh.asset_id = fah.ASSET_ID

and fab.CATEGORY_ID = fah.CATEGORY_ID

AND fdh.location_id = fl.location_id

GROUP BY --fah.ASSET_ID

        DECODE (fah.asset_type, 'NATU', fab.asset_cost_acct,fab.cip_cost_acct)

        ,(fl.segment1||'.'||fl.segment2||'.'||fl.segment3||'.'||fl.segment4||'.'||fl.segment5);

Thanks

This post has been answered by Paulzip on Apr 19 2017
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 17 2017
Added on Apr 19 2017
8 comments
6,342 views